Geek Answers Handbook

How to get previous day entries using tsql?

Do I need all the records for the last day?

Hi

Select * from table1 where tabledate > getdate() -1

With this query, I need to run exactly after midnight to get an accurate result. I need to run it in the daytime and get all the records for the previous day.

+5
source share
5 answers

In SQL Server 2005, this is usually the fastest way to convert date and time to date:

DATEADD(day, DATEDIFF(day, 0, yourDate), 0)

In your case, this is done only once, so in reality it does not really matter. But he gives 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)
+7
source

Check out this page. This is a great resource for calculating dates.

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

+1

DATEDIFF:

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

1 .

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

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

Try the following:

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

More articles:


All Articles