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.

0

13 Answers

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

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

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

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

0

This should do it:

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

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)

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)

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.

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

A simple alternative

Select GETDATE() - 1

Change 1 to go back that many number of days

PS : This gives you timestamp accuracy.

This worked a charm:

SELECT * FROM mytable WHERE date(mydate) = DATE_SUB(CURDATE(), 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)