Read next day date not current date

[NKOS13]

Hi Below is a query that l would like it to read next day's date under Getdate . Please help with the correct fomular. Currently it reads todays date and data but l would like it to read next day as we do everything in advance( one day ahead).

SELECT OEORDH.ORDNUMBER, OEORDD.ITEM, OEORDD.[DESC], OEORDH.INVNUMBER, OEORDH.ORDDATE, OEORDD.QTYORDERED, OEORDD.AUDTUSER,
ICILOC.QTYONHAND - ICILOC.QTYSHNOCST + ICILOC.QTYRENOCST + ICILOC.QTYADNOCST AS QtyOnHand
FROM ICITEM INNER JOIN
ICILOC ON ICITEM.ITEMNO = ICILOC.ITEMNO INNER JOIN
OEORDH INNER JOIN
OEORDD ON OEORDH.ORDUNIQ = OEORDD.ORDUNIQ ON ICITEM.FMTITEMNO = OEORDD.ITEM
WHERE (DATEDIFF(dd, CONVERT(datetime, CAST(OEORDH.AUDTDATE AS NVARCHAR(10)) + N' 08:00:59.997', 112), GETDATE()) = 0) AND (OEORDD.LOCATION = 'G200') AND
(OEORDH.INVNUMBER = '*** NEW ***') AND (OEORDD.QTYSHIPPED = 0) AND (OEORDD.QTYORDERED <> 0) AND (ICILOC.LOCATION = 'G200')
ORDER BY OEORDD.ITEM

Your help is very much appreciated

 

[simian336]

Try

SELECT OEORDH.ORDNUMBER, OEORDD.ITEM, OEORDD.[DESC],
OEORDH.INVNUMBER, OEORDH.ORDDATE, OEORDD.QTYORDERED, OEORDD.AUDTUSER,
ICILOC.QTYONHAND - ICILOC.QTYSHNOCST + ICILOC.QTYRENOCST + ICILOC.QTYADNOCST AS QtyOnHand
FROM ICITEM
INNER JOIN ICILOC
ON ICITEM.ITEMNO = ICILOC.ITEMNO
INNER JOIN OEORDH
INNER JOIN OEORDD
ON OEORDH.ORDUNIQ = OEORDD.ORDUNIQ
ON ICITEM.FMTITEMNO = OEORDD.ITEM
WHERE DATEDIFF(dd, CONVERT(datetime, CAST(OEORDH.AUDTDATE AS NVARCHAR(10))
+ N' 08:00:59.997', 112), GETDATE()+1) = 0)
AND (OEORDD.LOCATION = 'G200')
AND (OEORDH.INVNUMBER = '*** NEW ***') AND (OEORDD.QTYSHIPPED = 0)
AND (OEORDD.QTYORDERED <> 0) AND (ICILOC.LOCATION = 'G200')
ORDER BY OEORDD.ITEM

Simi

 

[NKOS13]

It still does not work.

 

[markros]

Are you using SQL Server 2005 or less or SQL Server 2008?

declare @StartDate datetime, @EndDate datetime

select @StartDate = dateadd(day, datediff(day,'19000101',getdate()),
'19000102') -- tomorrow,
@EndDate = dateadd(day, datediff(day,'19000101',getdate()),
'19000103')--two days ahead

SELECT OEORDH.ORDNUMBER, OEORDD.ITEM, OEORDD.[DESC],
OEORDH.INVNUMBER, OEORDH.ORDDATE, OEORDD.QTYORDERED, OEORDD.AUDTUSER,
ICILOC.QTYONHAND - ICILOC.QTYSHNOCST + ICILOC.QTYRENOCST + ICILOC.QTYADNOCST AS QtyOnHand
FROM ICITEM 
INNER JOIN ICILOC 
    ON ICITEM.ITEMNO = ICILOC.ITEMNO 
INNER JOIN  OEORDH 
INNER JOIN  OEORDD 
    ON OEORDH.ORDUNIQ = OEORDD.ORDUNIQ 
    ON ICITEM.FMTITEMNO = OEORDD.ITEM
WHERE OEORDH.AUDTDATE>=@StartDate and OEORDH.AUDTDATE < @EndDate
AND (OEORDD.LOCATION = 'G200') 
AND (OEORDH.INVNUMBER = '*** NEW ***') AND (OEORDD.QTYSHIPPED = 0) 
AND (OEORDD.QTYORDERED <> 0) AND (ICILOC.LOCATION = 'G200')
ORDER BY OEORDD.ITEM

PluralSight Learning Library

 

[Olaf Doschke]

GetDate()+1 should return now + 1 day, DateAdd(dd,1 GetDate()) would do the same. But as you do DateDiff, why not query for the Diff being 1 ?

Also, as you do a datediff on the date part in days, you don't need to convert your datetime field, setting the time portion to 08:00:59.997, a dd Diff is only taking the days into account, the diff of somedate 23:59:59 to nextdate 00:00:00 will also be 1, not 0.

Eg SELECT DateDiff(dd,'19000101 23:59:59.000','19000102 00:00:00.000') returns 1.

If you want the diff to respect the time and get fractions of days, do the diff in hours and divide by 24 to get days, or in minutes and divide by 24*60.

Bye, Olaf.