SQL statement to select all rows from previous day

Question

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.

Answer 1

get today no time:

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

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

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

Answer 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.

Answer 4

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

Answer 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

Answer 6

This should do it:

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

Answer 7

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)

Answer 8

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)

Answer 9

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.

Answer 10

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

Answer 11

A simple alternative

Select GETDATE() - 1

Change 1 to go back that many number of days

PS : This gives you timestamp accuracy.

Answer 12

This worked a charm:

SELECT * FROM mytable WHERE date(mydate) = DATE_SUB(CURDATE(), INTERVAL 1 DAY);

Answer 13

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)