Yesterday - SQL syntax

Transact-SQL
1

Why is it that I can use the following code to get dates less than or equal to yesterday -

where table.date <= dateadd(d, -1, getdate())

but to get equal to yesterday it returns no data

where table.date = dateadd(d, -1, getdate())

thanks
(SQL Server 2012)

2

If your table.date is a DATE it will not match GETDATE() (except at midnight) which is a DATETIME .

3

Thanks. Is it safe to use cast(cast(getdate() as date) as datetime)-1 ?

Checking the table, the field is actually DATETIME.

4

Does table.date have data in the time portion?

You do not need to cast a date to compare to a datetime. That is done implicitly, so CAST(GETDATE() AS DATE) -1 would work - or DATEADD(day, -1, CAST(GETDATE() AS DATE))

5

no, it's all 00:00:00:000

6

The usual recommendation is to use a combination of >= and < to get the data for a specific date range. For example, to get all records for yesterday,

table.date >= CAST(DATEADD(dd,-1,GETDATE() AS DATE)
AND table.date < CAST(GETDATE() AS DATE)

This will eliminate any issues that may arise when there is time portion in the table.date, as well as issues caused by precision of various datetime data types.

7

Using a specific data type -- such a date or datetime -- only works on later versions of SQL that specifically check for that in the optimizer.

Best when dealing with singular values / variables is to use format 'YYYYMMDD', which SQL will always implicitly convert to the column's format, regardless of SQL version. Converting a few values is trivial as far as overhead. You wouldn't want to do this for a table with millions of rows:

table.date >= CONVERT(char(8), GETDATE(), 112) AND
table.date < CONVERT(char(8), DATEADD(DAY, 1, GETDATE()), 112)