Ever noticed some “odd” predicates in execution plans using GETDATE()-n where “n” is the number of days? Things like:
SOME_DATETIME_FIELD >= GETDATE()-‘1900-01-02 00:00:00.000’
It may look a bit odd but it does make sense where you think about the data type precedence rules in SQL Server. In this case, as GETDATE() is a DATETIME, there is an implicit conversion of the “n” into a DATETIME. So, if we run:
SELECT CAST(1 AS DATETIME)
what do we get? ‘1900-01-02 00:00:00.000’
And yes, “2” gives ‘1900-01-03 00:00:00.000’ as you might expect.
As the subtraction of the two dates is actually just using decimal subtraction, the GETDATE()-1 does work to give you “yesterday”. It’s just not quite as clear (in my view) as using:
DATEADD(day,-1,GETDATE())