how to get records of previous day using tsql?
17,240
Solution 1
In SQL Server 2005, this is generally the fastest way to convert a datetime to a date:
DATEADD(day, DATEDIFF(day, 0, yourDate), 0)
In your case, it's done only once, so the how doesn't really matter much. But it does give the following query.
Select
*
from
table1
where
tabledate >= DATEADD(day, DATEDIFF(day, 0, getDate()) - 1, 0)
AND tabledate < DATEADD(day, DATEDIFF(day, 0, getDate()), 0)
Solution 2
Check this page out. It is a great resource for calculating dates.
Author by
User13839404
Updated on June 08, 2022Comments
-
User13839404 almost 2 years
I need all the records from last day?
Hi
Select * from table1 where tabledate > getdate() -1
with this query, i need to run is exactly after midnight to get exact result. I need to run it in day time and get all the previous day's records.
-
MatBailie about 12 yearsAnd it will also bludgeon the optimiser with a huge rock. If the
tabledate
field is indexed, this version will not be able to use a range seek on the index. Instead it will need to scan the whole index calculating the DATEDIFF() on every unique value. This is because the field being searched is now hidden inside your function call. The alternative is slightly longer, but keeps all the function calls on constants, and so allows the much faster seek.