SQL SERVER – Puzzle – Adding Two Dates Resulting Strange Results

This puzzle, I have discovered accidentally. In one of the recent project, I had to display two dates next to each other. Now, when I was doing this, I accidentally did a mistake while I was typing that out. Indeed, this puzzle about strange results when adding two dates.

What I actually wanted to do was-

SELECT @date1, @date2

However, when I ended up typing was

SELECT @date1+@date2

Now I think it is very strange as the comma and the plus signs are nowhere near on a typewriter and I had ended up making this mistake.

The good thing is that I ended up learning something new which helped me build this puzzle.


Let us execute the following code

SELECT @date1='2010-01-20', @date2='2016-10-22'
SELECT @date1+@date2 AS result 

When I executed above code I ended up with the following result-

Now my question is how come we see the result as some date in the year 2126 and not an error?

Let me know what is your answer in the comments section. I will keep all the answers hidden till Monday.

If you like such puzzles, here are three puzzles which I had published in the recent time-

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Puzzle – Shortest Code to Produce the Number 1000000000 (One Billion)
Next Post
SQL SERVER – Puzzle – Brain Teaser – Changing Data Type is Changing the Default Value

Related Posts

29 Comments. Leave new

  • The starting date of SQL server is 1900-01-01.

    While adding 2 dates, it will returns date calculated from 1900-01-01: (Day count from 1900-01-01 to Date1) + (Day count from 1900-01-01 to Date2)

    SELECT @date1=’2010-01-20′, @date2=’2016-10-22′

    SELECT DATEDIFF(DAY,’1900-01-01′,@date1)
    SELECT DATEDIFF(DAY,’1900-01-01′,@date2)

    SELECT DATEADD(DAY, 0, DATEDIFF(DAY,’1900-01-01′,@date1)+DATEDIFF(DAY,’1900-01-01′,@date2))

    SELECT @date1+@date2 AS result

  • Dates are stored as difference from “January 1, 1900”.
    In case you are adding two dates,
    a.) first find no of days one date differ from “January 1, 1900”.
    b.) And add those day to second date variable.


    SELECT @date1=’2010-01-20′, @date2=’2016-10-22′

    SELECT DATEADD(DAY,DATEDIFF(DAY,’1900-01-01′,@date1),@date2) as TestResult1, SELECT @date1+@date2 AS Testresult2

  • Interesting that if you “port” this to excel, you get 2126-11-13
    01/20/2010 is 40198
    10/22/2016 is 42665
    sum is 82863 which formatted for date in excel is 2126-11-13

  • The + operator after date expression expects a number of days to add, and SQL convert the second expression @date2 to an integer number implicit, which adds 42663 days to the first date.
    == @date1+cast( @date2 as int) == @date1 + 42663 => ‘2126-11-11’


    • (fix my comment)

      The + operator after date expression expects a number of days to add, and SQL convert the second expression @date2 to number implicit, which adds 42663 days to the first date.
      == @date1+cast( @date2 as int) == @date1 + 42663 => ‘2126-11-11’

      (not integer)

      Izhar Azati

  • My guess is that SQL stores DATETIMES as offsets from 1900-01-01. Adding them together is equivalent to adding two integers, the result of which is being interpreted as a new DATETIME equal to the summation of the two values.

  • Steve Lightfoot
    October 23, 2017 3:28 pm

    Its because SQL Server stores dates as numbers. For example:-

    select cast(0 as datetime)

    This returns 1900-01-01 00:00:00.000

    This means that when you add two dates together you are in fact adding the date values together.

    select cast(cast(‘20100120’ as datetime) as int)

    Returns 40196

    select cast(cast(‘20161022’ as datetime) as int)

    Returns 42663

    40196 + 42663 = 82859


    select cast(82859 as datetime)

    Returns 2126-11-11 00:00:00.000

    This can be really useful in code as you don’t need to use the dateadd function when adding or subtracting days from a date as you can use the +/- operators instead.

    For example:-

    select getdate()+1

    Returns the same results as select dateadd(day,1,getdate()) but with less typing.

  • Tim Cartwright
    October 23, 2017 11:05 pm

    To get your 2016 date: SELECT cast(21130 AS datetime) + cast(21329 AS datetime)

    As everyone else stated dates are truly numbers, and performing the plus operator on them just adds the two numbers.


Leave a Reply Cancel reply

Exit mobile version