选择前一天的所有行的 SQL 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1503298/
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
SQL statement to select all rows from previous day
提问by rudimenter
I am looking for a good SQL Statement to select all rows from the previous day from one table. The table holds one datetime column. I am using SQL Server 2005.
我正在寻找一个好的 SQL 语句来从一个表中选择前一天的所有行。该表包含一个日期时间列。我正在使用 SQL Server 2005。
回答by KM.
get today no time:
今天没有时间：
SELECT dateadd(day,datediff(day,0,GETDATE()),0)
get yestersday no time:
获得昨天没有时间：
SELECT dateadd(day,datediff(day,1,GETDATE()),0)
query for all of rows from only yesterday:
仅查询昨天的所有行：
select
*
from yourTable
WHERE YourDate >= dateadd(day,datediff(day,1,GETDATE()),0)
AND YourDate < dateadd(day,datediff(day,0,GETDATE()),0)
回答by Konamiman
To get the "today" value in SQL:
要在 SQL 中获取“今天”值：
convert(date, GETDATE())
To get "yesterday":
要获得“昨天”：
DATEADD(day, -1, convert(date, GETDATE()))
To get "today minus X days": change the -1 into -X.
要获得“今天减去 X 天”：将 -1 更改为 -X。
So for all yesterday's rows, you get:
所以对于所有昨天的行，你得到：
select * from tablename
where date >= DATEADD(day, -1, convert(date, GETDATE()))
and date < convert(date, GETDATE())
回答by call me Steve
It's seems the obvious answer was missing. To get all data from a table (Ttable) where the column (DatetimeColumn) is a datetime with a timestamp the following query can be used:
似乎缺少明显的答案。要从表 (Ttable) 中获取所有数据，其中列 (DatetimeColumn) 是带有时间戳的日期时间，可以使用以下查询：
SELECT * FROM Ttable
WHERE DATEDIFF(day,Ttable.DatetimeColumn ,GETDATE()) = 1 -- yesterday
This can easily be changed to today, last month, last year, etc.
这可以很容易地更改为今天、上个月、去年等。
回答by Romancha KC
SELECT * from table_name where date_field = DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY);
回答by Rahul
Its a really old thread, but here is my take on it. Rather than 2 different clauses, one greater than and less than. I use this below syntax for selecting records from A date. If you want a date range then previous answers are the way to go.
它是一个非常古老的线程，但这是我的看法。而不是两个不同的子句，一个大于和小于。我使用以下语法从 A 日期中选择记录。如果你想要一个日期范围，那么以前的答案就是要走的路。
SELECT * FROM TABLE_NAME WHERE
DATEDIFF(DAY, DATEADD(DAY, X , CURRENT_TIMESTAMP), <column_name>) = 0
In the above case X will be -1 for yesterday's records
在上述情况下，昨天的记录 X 将为 -1
回答by Mark Bell
Can't test it right now, but:
现在无法测试，但是：
select * from tablename where date >= dateadd(day, datediff(day, 1, getdate()), 0) and date < dateadd(day, datediff(day, 0, getdate()), 0)
回答by candleHyman
This should do it:
这应该这样做：
WHERE `date` = CURDATE() - INTERVAL 1 DAY
回答by Mário Meyrelles
In SQL Server do like this:
在 SQL Server 中这样做：
where cast(columnName as date) = cast(getdate() -1 as date)
You should cast both sides of the expression to date to avoid issues with time formatting.
您应该将表达式的两边都转换为日期以避免时间格式问题。
If you need to control interval in more detail, then you should try something like:
如果您需要更详细地控制间隔，那么您应该尝试以下操作：
declare @start datetime = cast(getdate() - 1 as date)
declare @end datetime = cast(getdate() - 1 as date)
set @end = dateadd(second, 86399, @end)
回答by Amey P Naik
Well, its easier to cast the datetime column to date and than compare.
好吧，将日期时间列转换为日期比比较更容易。
SELECT * FROM TABLE_NAME WHERE cast(COLUMN_NAME as date) =
dateadd(day,0, convert(date, getdate(), 105))
回答by user3428292
Another way to tell it "Yesterday"...
告诉它“昨天”的另一种方式......
Select * from TABLE
where Day(DateField) = (Day(GetDate())-1)
and Month(DateField) = (Month(GetDate()))
and Year(DateField) = (Year(getdate()))
This conceivably won't work well on January 1, as well as the first day of every month. But on the fly it's effective.
可以想象，这在 1 月 1 日以及每个月的第一天效果不佳。但在飞行中它是有效的。
