Server在where子句中生成日期字段数据

Question

我的疑问是

Select * from Orders 
where ordersdate Between '2013-10-10 00:00:00.00' and '2013-10-10 23:59:59.997'

我需要每天运行这个查询，它应该是前一天的日期。因此，如何以上述格式生成日期是我正在努力解决的问题。

Answer 1

DECLARE @StartDate DATETIME, @EndDate DATETIME

/* set the end date to midnight of the previous day */
SET @EndDate = CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 121), 121) 
/* set the start date to 1 day previously (thereby getting midnight to midnight) */
SET @StartDate = DATEADD(day, -1, @EndDate)

/* you could use between, but orders placed at midnight might be counted twice (on subsequent day reports) */
Select * from Orders where ordersdate Between @StartDate AND @EndDate

/* or you could use >= and < to guarantee that a order placed at midnight will only be counted once */
Select * from Orders where ordersdate >= @StartDate AND ordersdate < @EndDate

Answer 2

Select * 
from Orders
where ordersdate >= cast(dateadd(d, -1, getdate()) as date)
and ordersdate < cast(getdate() as date)

替代附加的time，您可以使用>=昨天的日期和今天的<。

Answer 3

select * 
from orders 
where datediff(d, ordersdate, getdate()) = 1

/编辑/按Jason注释--这样的查询很容易编写和理解，但是依赖于行数可能会变得非常无效。