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.

SQL SERVER - Puzzle - Adding Two Dates Resulting Strange Results puzzledatedate-800x398

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.

Puzzle:

Let us execute the following code

DECLARE @date1 DATETIME, @date2 DATETIME
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-

SQL SERVER - Puzzle - Adding Two Dates Resulting Strange Results datetimepuzzle

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)

    Ex:
    DECLARE @date1 DATETIME, @date2 DATETIME
    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

    Reply
  • 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.

    E.g

    DECLARE @date1 DATETIME, @date2 DATETIME
    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

    Reply
  • 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

    Reply
  • 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’

    https://docs.microsoft.com/en-us/sql/t-sql/language-elements/add-transact-sql?view=sql-server-2017

    Reply
    • (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

      Reply
  • 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.

    Reply
  • 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

    Therefore:-

    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.

    Reply
  • 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.

    Reply

Leave a Reply

Menu