13

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?

14

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.

8

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.

  • It's more explicitly stated here: Arithmetic Operators (Transact-SQL) "The plus (+) and minus (-) operators can also be used to perform arithmetic operations on datetime and smalldatetime values." – ypercubeᵀᴹ Oct 18 '17 at 12:06
  • @ypercubeᵀᴹ: The first line of - (Subtract) (Transact-SQL) is even more explicit w.r.t the unit (days): "Subtracts two numbers (an arithmetic subtraction operator). Can also subtract a number, in days, from a date." – Heinzi Oct 18 '17 at 14:40
  • yes I saw that. And then later that " Cannot be used with date, time, datetime2, or datetimeoffset data types." So the "date" in the first sentence means any date/time type except those that it is forbidden (so only datetime and smalldatetime, basically the datetime types that existed before 2008 (?) version that date was added) . It's a bit messy. – ypercubeᵀᴹ Oct 18 '17 at 17:39
  • Perhaps that's another thing you can add in your answer. The fact that this operator does not support the new types suggests it has been kept only for reasons of backward compatibility. – ypercubeᵀᴹ Oct 18 '17 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.