I was wondering what the difference between the following two methods is:
I guess using
DATEADD is the correct way, but was wondering why?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
There's no real difference there, but when you start using
DATETIME2 values, or functions that return
DATETIME2 values, you'll get errors.
SELECT SYSDATETIME() - 1 AS [Incompatible]
Msg 206, Level 16, State 2, Line 17 Operand type clash: datetime2 is incompatible with int
For these, you have to use date math functions.
SELECT DATEADD(DAY, -1, SYSDATETIME()) AS [Compatible]
Aaron Bertrand speaks about this issue briefly in his Bad Habits to Kick series.
Contrary to claims in one of the other answers, both options are officially supported and documented by SQL Server:
datetime - number it is not undefined behavior.
The big advantage of
DATEADD(d, -2, GETUTCDATE())
is the fact that it is self-documenting: It's purpose is immediately obvious.
GETUTCDATE() - 2, on the other hand, relies on the reader knowing the definition of the
datetime - number operation. Yes, it might currently be idiomatic T-SQL, but the fact that this is no longer supported for
datetime2 implies that future generations of SQL Server developers might no longer be familiar with it.