CODEWITHSUNDEEP
sqlsql-serversql-server-2005
rudimenter
rudimenter

Reputation: 3450

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.

Upvotes: 153

Views: 542255

Answers (13)

Amey P Naik
Amey P Naik

Reputation: 718

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

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

Upvotes: 0

Mark Bell
Mark Bell

Reputation: 29735

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)

(Late remark: this has now been checked, it works.)

Upvotes: 4

KM.
KM.

Reputation: 103589

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)

Upvotes: 265

eva
eva

Reputation: 59

This worked a charm:

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

Upvotes: -1

viru
viru

Reputation: 2349

A simple alternative

Select GETDATE() - 1

Change 1 to go back that many number of days

PS : This gives you timestamp accuracy.

Upvotes: -1

Dismi Paul
Dismi Paul

Reputation: 205

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)

Upvotes: -2

M&#225;rio Meyrelles
M&#225;rio Meyrelles

Reputation: 1792

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)

Upvotes: 3

candlejack
candlejack

Reputation: 1209

This should do it:

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

Upvotes: 4

call me Steve
call me Steve

Reputation: 1727

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.

Upvotes: 19

user3428292
user3428292

Reputation: 1

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.

Upvotes: 1

Romancha KC
Romancha KC

Reputation: 1527

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

Upvotes: 13

Rahul
Rahul

Reputation: 1027

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

Upvotes: 4

Konamiman
Konamiman

Reputation: 50273

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

Upvotes: 64

Related Questions