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.

Possible duplicate of [SQL statement to select all rows from previous day](https://stackoverflow.com/questions/1503298/sql-statement-to-select-all-rows-from-previous-day) — underscore_d, Aug 01 '17 at 15:06

score 9 · Accepted Answer · answered Feb 14 '12 at 16:03

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)

score 1 · Answer 2 · answered Feb 14 '12 at 16:06

1

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

answered Feb 14 '12 at 16:06

Ashwin Chandran

1,447
14
11

score 0 · Answer 3 · edited Oct 03 '13 at 14:58

0

Try this:

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

edited Oct 03 '13 at 14:58

Smern

18,746
21
72
90

answered Oct 03 '13 at 14:38

jamie

1

score 0 · Answer 4 · answered Feb 14 '12 at 16:16

0

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.

answered Feb 14 '12 at 16:16

Bort

7,398
3
33
48

3

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***. – MatBailie Feb 14 '12 at 16:19

score 0 · Answer 5 · answered Feb 14 '12 at 16:17

0

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

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

answered Feb 14 '12 at 16:17

Aaron Bertrand

272,866
37
466
490

how to get records of previous day using tsql?

5 Answers5