andrewd.smith (11/10/2010)
Either of these will work and stand a chance of using an index (if such an index exists) on the OrderDate column:
SELECT *FROM dbo.ZNodeOrder NO
INNER JOIN dbo.ZNodeOrderLineItem NOLI
ON (NO.OrderID = NOLI.OrderID)
WHERE (NO.CardTransactionID IS NOT NULL)
AND (OrderDate BETWEEN
DATEADD(hour, -1, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
AND DATEADD(hour, 11, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)))
AndrewD, try this:
DECLARE @Now DATETIME
SET @Now = DATEADD(hh, -9, GETDATE() )
SELECT [Now] = @Now,
StartDate = DATEADD(hour, -1, DATEADD(day, DATEDIFF(day, 0, @Now), 0)),
EndDate = DATEADD(hour, 11, DATEADD(day, DATEDIFF(day, 0, @Now), 0)),
ExpectedEnddate = '2010-11-09 23:00:00.000'
SET @Now = DATEADD(hh, +9, GETDATE() )
SELECT [Now] = @Now,
StartDate = DATEADD(hour, -1, DATEADD(day, DATEDIFF(day, 0, @Now), 0)),
EndDate = DATEADD(hour, 11, DATEADD(day, DATEDIFF(day, 0, @Now), 0)),
ExpectedEnddate = '2010-11-10 23:00:00.000'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden