Add datetime and time

Question

Server: SQL Server 2012; SP1; Developer Edition

Code:

declare @datetime datetime = '1900-01-01 00:00:00.000'

declare @time time = '11:11:11'

select @datetime + @time

When I run the above code in the MASTER database, I get the error:

Msg 402, Level 16, State 1, Line 3 The data types datetime and time are incompatible in the add operator.

But when it's any other database, it works! Any idea why this must be happening?

P.S. - In the enterprise edition, this throws an error irrespective of the database context.

Answer 1

The above code will work with MS SqlServer 2008 but this code won't work with MS SqlServer 2012 or above. I have encountered the same issue and solve it like this.

DECLARE @today_start datetime
DECLARE @dail_time time   

SELECT @today_start = convert(datetime, @dayStr,103) + CAST(@dail_time as DATETIME)

Answer 2

Actually, there is no rational behavior when adding a date and a time, unless you know the timezones for both values. Other databases may assume some arbitrary default timezone but SQL Server doesn't.

Had the T-SQL designers added such support, the majority of old code (both server and client-side) would break with hard to detect errors. Remember, most people still use the old datetime type, assuming some default timezone (local or GMT).

You could argue that adding a datetimeoffset and a time should be possible, because the first contains timezone info and the second doesn't, but I think the T-SQL designer wanted to avoid confusion when converting one datatype to the other. A lot of client-side code would still break unless people changed their client-side types timezone aware code.

So, why no explicit function that does the addition? Probably wouldn't work either, as datetime types without timezone get converted implicitly to datetimeoffset2. You could easily pass a value without timezone to such function and it would get converted to the wrong timezone BEFORE it was passed to the function

In short, backwards compatibility doesn't provide a safe way handle date+time addition.