TSQL SELECT previous date's records

Question

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.

Solution 1

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?

Solution 2

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

Solution 3

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.

Comments

[Adriaan Davel]

Casting the column could have serious performance implications, see JanW's solution below

[Mikael Eriksson]

@AdriaanDavel Yes it can, with cardinality estimates. Not with sargability. Did you read the question on DBA linked from my answer?