SQL statement to select all rows from previous day

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.

Solution 1:

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)

Solution 2:

To get the "today" value in SQL:

convert(date, GETDATE())

To get "yesterday":

DATEADD(day, -1, convert(date, GETDATE()))

To get "today minus X days": change the -1 into -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())

Solution 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:

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

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

Solution 4:

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

Solution 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