CODEWITHSUNDEEP
sqlsql-server-2005t-sql
User13839404
User13839404

Reputation: 1813

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.

Upvotes: 8

Views: 18947

Answers (5)

jamie
jamie

Reputation: 1

Try this: 

your_field = cast(dateadd(D,-1,getdate()) as DATE)

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

DECLARE @d SMALLDATETIME;
SET @d = DATEDIFF(DAY, 0, GETDATE());

SELECT <cols> FROM dbo.table1
  WHERE tabledate >= DATEADD(DAY, -1, d)
  AND tabledate < @d;

Upvotes: 0

Bort
Bort

Reputation: 7638

Another method is to use DATEDIFF alone:

SELECT * FROM table1
WHERE DATEDIFF(DAY, tabledate, GETDATE()) = 1

A datediff of 1 for day covers any time in the previous day.

Upvotes: 0

Ashwin Chandran
Ashwin Chandran

Reputation: 1467

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

Upvotes: 1

MatBailie
MatBailie

Reputation: 86808

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)

Upvotes: 9

Related Questions