El Gato (11/10/2010)
Hi guys, our developer is not in today and I need help to generate the following query in a dynamic fashion:
SELECT *FROM dbo.ZNodeOrder NO
INNER JOIN dbo.ZNodeOrderLineItem NOLI
ON NO.OrderID = NOLI.OrderID
WHERE NO.CardTransactionID IS NOT NULL
AND convert(char(10), OrderDate, 120) >= '2010-11-09 23:00:00'
AND convert(char(10), OrderDate, 120) <= '2010-11-10 11:00:00'
Now this works in a static way but I need it to be able to generate results on a daily basis whereby it can return orders from 11pm on the previous day, to 11am on the current day and then again from 11am to 11pm on the current day.
Currently I can only get this running by having two separate jobs and manually modifying the dates and times which isn't ideal.
Thanks for reading and I hope you guys can help.
This Should do the trick
SELECT *
FROM dbo.ZNodeOrder NO
INNER JOIN dbo.ZNodeOrderLineItem NOLI
ON NO.OrderID = NOLI.OrderID
WHERE NO.CardTransactionID IS NOT NULL
AND convert(char(10), OrderDate, 120) >= cast(dateadd(hh,23,cast(getdate()-1 as int) ) as datetime)
AND convert(char(10), OrderDate, 120) <= cast(dateadd(hh,11,cast(getdate() as int) ) as datetime)