从12小时前获得订单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..

我正在尝试从OrderDate（datetime）为12小时前的SQL Server数据库中提取记录。理想情况下，我想整整一个小时......比如..

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..

如果我现在运行查询，在21.38（我的时间），查询将拉出日期时间为9.00 - 10.00的记录我不是因为实际日期而烦恼，只是12小时前的订单..此查询将运行每小时一次，所以下次它运行时它将在22.38运行，并将从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))

2 个解决方案

#1

Use this where clause instead

请改用where子句

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)

#2

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.

更新：我重写了这个。请尝试运行此代码。如果它返回您期望的内容，则返回其他WHERE行。

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)

#1