How is this code adding a number to a datetime without using DATEADD? [closed]

In the following SQL statement there’s some T-SQL that adds a number to a datetime and to a date:

declare @t table(UserID int, StartDate datetime, EndDate date)
insert @t select 1, '20110101', '20110102' 

-- Adding int to a datetime works
select t.StartDate + 2 as NewStartDate

-- Adding int to a date errors
select t.EndDate + 2 as NewEndDate

I would expect both of the above select statements to fail because neither are using the DATEADD method, but to my surprise adding an int to a datetime without using DATEADD works. Why is that?

Answer

You can add a number to a datetime. It is interpreted as a number of days.

Perhaps surprisingly, SQL Server does not support adding an integer to date.

So, this works:

select getdate() + 1

But this generates an error:

select cast(getdate() as date) + 1

This is sort of explained in the documentation for + (operator):

expression

Is any valid expression of any one of the data types in the numeric category except the bit data type. Cannot be used with date, time, datetime2, or datetimeoffset data types.

I mean, anyone reading this would immediately notice that datetime is in the “numeric” category and missing from the list of types that are not supported.

I should note that this functionality also allows adding two datetime values. This is handy for adding a “time” (cast to datetime) to a date (cast to datetime).