SQL statement to select all rows from the previous day

Question

I am looking for a good SQL statement to select all rows from the previous day from a single table. The table contains one datetime column. I am using SQL Server 2005.

Answer 1

There is no time today:

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

get yestersday no time:

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

query for all rows just 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 value of "today" in SQL:

 convert(date, GETDATE()) 

To get yesterday:

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

To get “today minus X days”: change the value of -1 to -X.

So for all yesterday's lines you get:

 select * from tablename where date >= DATEADD(day, -1, convert(date, GETDATE())) and date < convert(date, GETDATE()) 

Answer 3

There seems to be no obvious answer. To get all the data from a table (Ttable), where a column (DatetimeColumn) is a date-time with a timestamp, you can use the following query:

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

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

Answer 4

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

Answer 5

This is a really old thread, but here is my approach to it. Instead of 2 different sentences, one is more than less. I use this syntax below to select records from a date. If you need a date range, then the 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 entries

Answer 6

Can't check 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 7

This should do it:

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

Answer 8

In SQL Server, follow these steps:

 where cast(columnName as date) = cast(getdate() -1 as date) 

You should use both sides of the expression today to avoid time formatting problems.

If you need to control the interval in more detail, 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 talk about it is "Yesterday" ...

 Select * from TABLE where Day(DateField) = (Day(GetDate())-1) and Month(DateField) = (Month(GetDate())) and Year(DateField) = (Year(getdate())) 

This, obviously, will not work well on January 1, as well as on the first day of each month. But on the fly it is effective.

Answer 10

Well, it's easier to cast the datetime column to a date and compare.

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

Answer 11

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

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