CODEWITHSUNDEEP
sqlsql-servert-sqldate
MichaelEaton
MichaelEaton

Reputation: 195

Get orders from 12 hours ago SQL Server

I'm trying to pull records from a SQL Server database where the OrderDate (datetime) was 12 hours ago. Ideally I'd like to do the whole hour.. for instance..

If I run the query now, at 21.38 (my time) the query would pull records that have the datetime of 9.00 - 10.00 I'm not bothered about the actual date as such, just orders from 12 hours ago.. this query will run every hour, so the next time it ran it would run at 22.38 and would pull orders from 10.00 - 11.00..

This is what I've tried:

SELECT TOP (100) PERCENT 
    dbo.[Order].OrderID, 
    dbo.Customer.Forename, 
    dbo.Customer.Surname, 
    dbo.Customer.Email, 
    dbo.[Order].OrderDate, 
    dbo.[Order].OrderStatusID, 
    dbo.[Order].WebsiteID, 
    dbo.Addresses.CountryID
FROM dbo.[Order] 
INNER JOIN dbo.Customer 
    ON dbo.[Order].CustomerID = dbo.Customer.CustomerID
INNER JOIN dbo.Addresses 
    ON dbo.[Order].BillingAddressID = dbo.Addresses.AddressID 
    AND dbo.[Order].DeliveryAddressID = dbo.Addresses.AddressID
    AND dbo.Customer.CustomerID = dbo.Addresses.CustomerID
WHERE (dbo.[Order].WebsiteID IN (1, 2, 8, 12)) 
  AND (dbo.[Order].OrderStatusID = 1) 
  AND (dbo.[Order].OrderDate >= DATEADD(hour, - 12, GETDATE())) 
  AND (dbo.[Order].OrderDate <= DATEADD(hour, - 11, GETDATE()))

Update:

This is the correct query I'm currently trying

SELECT     TOP (100) PERCENT dbo.[Order].OrderID, dbo.Customer.Forename,     dbo.Customer.Surname, dbo.Customer.Email, dbo.[Order].OrderDate,     dbo.[Order].OrderStatusID, dbo.[Order].WebsiteID, 
                       dbo.Addresses.CountryID
FROM         dbo.[Order] INNER JOIN
                      dbo.Customer ON dbo.[Order].CustomerID = dbo.Customer.CustomerID     LEFT OUTER JOIN
                      dbo.Addresses ON dbo.[Order].DeliveryAddressID =     dbo.Addresses.AddressID AND dbo.Customer.CustomerID = dbo.Addresses.CustomerID
WHERE     (dbo.[Order].WebsiteID IN (1, 2, 8, 12)) AND (dbo.[Order].OrderStatusID = 1)     AND (dbo.[Order].OrderDate >= DATEADD(Hour, DATEDIFF(Hour, 0, GETDATE()) - 12, 0))     AND 
                      (dbo.[Order].OrderDate < DATEADD(Hour, DATEDIFF(Hour, 0,     GETDATE()) - 11, 0))

Upvotes: 1

Views: 15173

Answers (2)

tommy_o
tommy_o

Reputation: 3783

This should work (and I cleaned up your whitespace a bit):

declare @topofhour datetime
SELECT @topofhour = dateadd(hour,-11,dateadd(hour,datediff(hour,0,getdate()),0))

--checking against the provided scenario, since i've buggered this up twice
--
--declare @topofhour  datetime 
--select @topofhour  = '05/17/2013 21:38:00'
--select @topofhour  = dateadd(hour,-11,dateadd(hour,datediff(hour,0,@topofhour ),0))
--select dateadd(hour,-1,@topofhour), @topofhour
--
-- -- results are `2013-05-17 09:00:00.000,2013-05-17 10:00:00.000`

SELECT     TOP (100) PERCENT dbo.[Order].OrderID
            , dbo.Customer.Forename
            , dbo.Customer.Surname
            , dbo.Customer.Email
            , dbo.[Order].OrderDate
            , dbo.[Order].OrderStatusID
            , dbo.[Order].WebsiteID
            , dbo.Addresses.CountryID 
FROM         dbo.[Order] 
INNER JOIN  dbo.Customer 
ON          dbo.[Order].CustomerID = dbo.Customer.CustomerID 
INNER JOIN  dbo.Addresses
AND         dbo.[Order].DeliveryAddressID = deliveryAddress.AddressID 
AND         dbo.Customer.CustomerID = deliveryAddress.CustomerID
WHERE       (dbo.[Order].WebsiteID IN (1, 2, 8, 12)) 
AND         (dbo.[Order].OrderStatusID = 1) 
AND         (dbo.[Order].OrderDate between dateadd(hour,-1,@topofhour) and @topofhour)

UPDATE: I redid this. Please try running this instead. If it returns what you expect, then add the other WHERE lines back. 

DECLARE     @topDate datetime, @bottomDate datetime

SELECT      @topDate        = DATEADD(Hour, DATEDIFF(Hour, 0, GETDATE()) - 12, 0)
            ,@bottomDate    = DATEADD(Hour, DATEDIFF(Hour, 0, GETDATE()) - 11, 0)

SELECT     TOP (100) PERCENT o.OrderID
            , c.Forename
            , c.Surname
            , c.Email
            , o.OrderDate
            , o.OrderStatusID
            , o.WebsiteID
            , a.CountryID
FROM        dbo.[Order] as o
left join   dbo.Customer as c
ON          o.CustomerID = c.CustomerID     
left join   dbo.Addresses as a
ON          o.DeliveryAddressID = a.AddressID 
AND         c.CustomerID = a.CustomerID
WHERE       (o.OrderDate BETWEEN @bottomDate AND @topDate)
--AND         (o.WebsiteID IN (1, 2, 8, 12)) 
--AND         (o.OrderStatusID = 1)

Upvotes: 0

George Mastros
George Mastros

Reputation: 24498

Use this where clause instead

WHERE   dbo.[Order].WebsiteID IN (1, 2, 8, 12) 
        AND dbo.[Order].OrderStatusID = 1 
        AND OrderDate >=  DateAdd(Hour, DateDiff(Hour, 0, GetDate())-12, 0)
        AND OrderDate <   DateAdd(Hour, DateDiff(Hour, 0, GetDate())-11, 0)

Upvotes: 9

Related Questions