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.
12 Answers
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
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.
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)
Related