CODEWITHSUNDEEP
sqlsql-server-2005t-sqldatetime
Alex
Alex

Reputation: 3079

TSQL SELECT previous date's records

I want to select all records from a table Log where the DateAndTime field values (of type datetime) are for the day before today, whatever day it is.

So if today is 2011-06-08, I want to select all rows where DateAndTime is greater than or equal to 2011-06-07 00:00:00 and also less than 2011-06-08 00:00:00.

I'm guessing the potential pitfall here would be it's behaviour on the 1st day of the month, as obviously a date like 2011-06-00 is invalid, and should be 2011-05-31.

Upvotes: 18

Views: 41771

Answers (5)

JanW
JanW

Reputation: 1849

SELECT * FROM Log
WHERE DateAndTime >= DATEADD(DAY,-1, CAST(GETDATE() AS DATE))
AND DateAndTime < CAST(CAST(GETDATE() AS DATE) AS DATETIME)

Upvotes: 3

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

For SQL Server 2008 you can use this.

select *
from [log]
where cast(DateAndTime as date) = cast(getdate()-1 as date)

Pre 2008 you can use this

select *
from [log]
where DateAndTime >= dateadd(d, datediff(d, 0, getdate())-1, 0) and
      DateAndTime < dateadd(d, datediff(d, 0, getdate()), 0)

Related on DBA: Cast to date is sargable but is it a good idea?

Upvotes: 31

Pankaj
Pankaj

Reputation: 10115

It should include conditional operator and not between . Otherwise it includes today's records as well.

Declare @today date
Set @today = GETDATE()


Select YourcolumnNames from log
Where DateAndTime >= DATEADD(dd, -1, @today ) and DateAndTime < DATEADD(dd, -1, @today )

Moreover, you should mention the column name and * should be avoided in the select statement. This can improve the performance

Upvotes: 0

BonyT
BonyT

Reputation: 10940

Assuming SQL Server

 declare @today date
 set @today = GETDATE()

 select * from Log where DateAndTime between DATEADD(dd, -1, @today ) and @today

Upvotes: 0

Mutation Person
Mutation Person

Reputation: 30520

This example assumes SQL Server:

select *
from log
where convert(varchar(8), DateAndTime , 112)  = convert(varchar(8), getdate()-1, 112)

Essentially, convert the date to yyyymmdd (the 112 parameter) and then check it is equal to yesterday's date (getdate()-1), also converted to yyyymmdd.

Upvotes: 1

Related Questions