SQL TSQL SELECT 上一个日期的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6279440/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
TSQL SELECT previous date's records
提问by Alex
I want to select all records from a table Logwhere the DateAndTimefield values (of type datetime) are for the day before today, whatever day it is.
我想从一个表Log中选择所有记录，其中DateAndTime字段值（类型datetime）是今天的前一天，无论是哪一天。
So if today is 2011-06-08, I want to select all rows where DateAndTimeis greater than or equal to 2011-06-07 00:00:00and also less than 2011-06-08 00:00:00.
因此，如果今天是 2011-06-08，我想选择DateAndTime大于或等于2011-06-07 00:00:00且小于 的所有行2011-06-08 00:00:00。
I'm guessing the potential pitfall here would be it's behaviour on the 1st day of the month, as obviously a date like 2011-06-00is invalid, and should be 2011-05-31.
我猜这里的潜在缺陷是它在一个月的第一天的行为，因为很明显这样的日期2011-06-00是无效的，应该是2011-05-31.
回答by Mikael Eriksson
For SQL Server 2008 you can use this.
对于 SQL Server 2008，您可以使用它。
select *
from [log]
where cast(DateAndTime as date) = cast(getdate()-1 as date)
Pre 2008 you can use this
2008 年之前，您可以使用此
select *
from [log]
where DateAndTime >= dateadd(d, datediff(d, 0, getdate())-1, 0) and
DateAndTime < dateadd(d, datediff(d, 0, getdate()), 0)
Related on DBA: Cast to date is sargable but is it a good idea?
与 DBA 相关：迄今为止的演员阵容是可以讨论的，但这是一个好主意吗？
回答by JanW
SELECT * FROM Log
WHERE DateAndTime >= DATEADD(DAY,-1, CAST(GETDATE() AS DATE))
AND DateAndTime < CAST(CAST(GETDATE() AS DATE) AS DATETIME)
回答by James Wiseman
This example assumes SQL Server:
此示例假设 SQL Server：
select *
from log
where convert(varchar(8), DateAndTime , 112) = convert(varchar(8), getdate()-1, 112)
Essentially, convert the date to yyyymmdd (the 112parameter) and then check it is equal to yesterday's date (getdate()-1), also converted to yyyymmdd.
本质上，将日期转换为 yyyymmdd（112参数），然后检查它是否等于昨天的日期 ( getdate()-1)，也转换为 yyyymmdd。
回答by BonyT
Assuming SQL Server
假设 SQL Server
declare @today date
set @today = GETDATE()
select * from Log where DateAndTime between DATEADD(dd, -1, @today ) and @today
回答by Pankaj
It should include conditional operatorand not between. Otherwise it includes today's records as well.
它应该包括条件运算符而不是between。否则，它也包括今天的记录。
Declare @today date
Set @today = GETDATE()
Select YourcolumnNames from log
Where DateAndTime >= DATEADD(dd, -1, @today ) and DateAndTime < DATEADD(dd, -1, @today )
Moreover, you should mention the column name and * should be avoided in the select statement. This can improve the performance
此外，您应该提及列名，并且应该避免在 select 语句中使用 *。这可以提高性能
