Opinionated Software

… we have opinions about everything!

GETDATE()-1 —

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())


Categorised as: Hints and Tips | SQL | SQL Server



Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.