CODEWITHSUNDEEP
sql-servert-sqldatetime
Anthony Faull
Anthony Faull

Reputation: 17957

Why is the addition operator defined for DATETIME values but not for DATE?

I am aware that the workaround is to use DATEADD for both data types. I'd like to understand why the language designers chose to define this operator for one data type but not the other?

When you try using the plus operator directly on a DATE

DECLARE @tomorrow DATE = CONVERT(DATE, GETDATE()) + 1

you get this error message:

Msg 206, Level 16, State 2, Line 1
Operand type clash: date is incompatible with int

However you can add an integer to a DATETIME, and you can implicitly convert that DATETIME to a DATE with no errors:

DECLARE @thisTimeTomorrow DATETIME = GETDATE() + 1
DECLARE @tomorrow DATE = GETDATE() + 1

Upvotes: 6

Views: 2448

Answers (2)

John Bell
John Bell

Reputation: 2350

This is because SQL Server DATETIME behaviour is kept the same for compatibility purposes. Since version 2008 DATETIME2 was introduced, that prohibits the ability to add integers, DATE was also introduced at the same time, and this also prohibits addition.

The simplest solution would be to move the addition inside the CONVERT:

DECLARE @tomorrow DATE = CONVERT(DATE, GETDATE() + 1)

Although I would recommend using DATEADD rather using integer additions:

DECLARE @tomorrow DATE = CONVERT(DATE, DATEADD(DD, +1, GETDATE()))

Upvotes: 4

Dan Guzman
Dan Guzman

Reputation: 46203

Microsoft SQL Server originated from the Sybase code base where an addition operator can combine datetime with a numeric and yield a datatime. According to the ANSI SQL standard, the result of such an expression should yield an interval type (which doesn't exist in SQL Server).

The newer time, date, datetime2, datetimeoffset data types developed by Microsoft intentionally do not support this legacy behavior in favor of ANSI standards. The built-in DATEADD function must be used.

Upvotes: 2

Related Questions