Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how to get records of previous day using tsql?

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.

like image 408
User13839404 Avatar asked Feb 14 '12 16:02

User13839404


2 Answers

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)
like image 186
MatBailie Avatar answered Oct 17 '22 01:10

MatBailie


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

like image 29
Ashwin Chandran Avatar answered Oct 17 '22 00:10

Ashwin Chandran