SQL语句选择前一天的所有行

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语句，以便从一个表中选择前一天的所有行。该表保存一个datetime列。我正在使用SQL Server 2005。

10 个解决方案

#1

159

get today no time:

今天没有时间:

SELECT dateadd(day,datediff(day,0,GETDATE()),0)

get yestersday no time:

得到yestersday没有时间:

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)

#2

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())

#3

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:

似乎没有明显的答案。要从列(DatetimeColumn)为日期时间戳的表(Ttable)中获取所有数据，可以使用以下查询:

SELECT * FROM Ttable
WHERE DATEDIFF(day,Ttable.DatetimeColumn ,GETDATE()) = 1 -- yesterday

This can easily be changed to today, last month, last year, etc.

这很容易改变到今天，上个月，去年，等等。

#4

SELECT * from table_name where date_field = DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY);

#5

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.

这是一条很旧的线，但这是我的看法。而不是两个不同的子句，一个比一个大，一个小。我使用下面的语法来选择日期的记录。如果你想要一个日期范围，那么之前的答案是可行的。

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

#6

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)

#7

This should do it:

这应该这样做:

WHERE `date` = CURDATE() - INTERVAL 1 DAY

#8

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日，以及每个月的第一天都不会起什么作用。但是在飞行中它是有效的。

#9

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.

您应该将表达式的两边都转换为date，以避免时间格式问题。

If you need to control interval in more detail, then you should try something like:

如果您需要更详细地控制interval，那么您应该尝试以下方法:

declare @start datetime = cast(getdate() - 1 as date)
declare @end datetime = cast(getdate() - 1 as date)
set @end = dateadd(second, 86399, @end)

#10

Well, its easier to cast the datetime column to date and than compare.

嗯，将datetime列转换为date和compare比起来更容易。

SELECT * FROM TABLE_NAME WHERE cast(COLUMN_NAME as date) = 
   dateadd(day,0, convert(date, getdate(), 105))

#1

159