[sql] 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.

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

subdate(now(),1) will return yesterdays timestamp The below code will select all rows with yesterday's timestamp

Select * FROM `login` WHERE `dattime` <= subdate(now(),1) AND `dattime` > subdate(now(),2)

This should do it:

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

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)

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

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.

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.

In SQL Server do like this:

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)

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

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