Sql-server – GETUTCDATE()-2 vs DATEADD(d,-2,GETUTCDATE())

sql serversql server 2014t-sql

I was wondering what the difference between the following two methods is:

 GETUTCDATE()-2

and

  DATEADD(d,-2,GETUTCDATE())

I guess using DATEADD is the correct way, but was wondering why?

Best Answer

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.

Related Question