how to get records of previous day using tsql?

Question

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.

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.

http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/#calculatingdates

Comments

[MatBailie]

And 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.