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.


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-

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)

SQL DateTime, SQL Scripts, SQL Server
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

  • Michael Poppers
    October 17, 2017 7:16 am

    ‘+’ing DATETIMEs requires implicit conversion to INT, and the value is the #days since 1900-JAN-01. After the two values are added, the result is converted back to a DATETIME before being displayed.

  • Dates are stored as an offset from Jan 1 1900. The two values stored for the two dates are 110 and 116. Displayed result is (110+116)+1900=2126

  • Presumably this works because of the way in which datetimes values are stored. If memory serves, it’s some sort of integer.
    I think this would error out with datetime2.

  • Date is stored internally as an number (no of days from particular date fixed by the system). So when you add 2 dates both the no representation is added and result is converted to corresponding date.

  • I thinks it’s simple. SQL server saves dates in numeric format when we add 2 dates it adds the numeric values of those dates and resulting number is converted back to datetime.

  • For the date part, sql serve is storing a integer value, representing the number of days starting from 1900 Jan 1 (=0) till that day.

    So for 2010-01-20 = we have 40196 = datediff(day,0,@date1)
    for 2016-10-22 = we have 42663 = datediff(day,0,@date2)
    adding these 40196+42663 = 82859 , this is how many days are from 1900 Jan 1 ( which is =0)
    dateadd(day,82859 ,0) = 2126-11-11

  • Shashi Pal Saini
    October 17, 2017 2:34 pm

    This is because date is stored as float , there is a float no. corresponding to each value of dateTime variable
    Following script will verifies it


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

    Declare @F1 Float,@F2 Float

    SELECT @F1=CONVERT(Float,CONVERT(DateTime,@date1))

    SELECT @F2 =CONVERT(Float,CONVERT(DateTime,@date2))

    SELECT @F1 AS F1 , @F2 AS F2, @F1 +@F2 AS ‘F1+F2’ ,CONVERT(datetime,@F1+@F2) AS R1 ,@date1 + @date2 AS R2

  • Hello

    This is because internally SQL server stores the date as a 4 byte integer.

    For example if I did this

    declare @date1 datetime = getdate(),
    @date2 datetime = getdate() + 1

    select @date1+@date2

    result :
    2135-08-04 22:28:22.607

    then I convert

    declare @date1 datetime = convert(int,getdate()),
    @date2 datetime = convert(int ,getdate()) + 1

    select convert(int,@date1)+convert(int,@date2)


    43023 + 43024 = 86047

    then I convert again back to datetime

    select convert(datetime,86047)

    result :
    2135-08-04 00:00:00.000

    As you can see its exactly like the first result we got

  • Adriana Milcov
    October 17, 2017 5:35 pm

    SQL Server internally store datetime datatype as 8 bytes.
    The first 4 bytes are for storing years, relatively to 1900 year.
    The 1st variable @date1 has 1010 stored for year (2010-1900=1010) ,
    the 2nd vaiable @date2 has 1116 (2016-1900=1116)
    So the adding the 2 variables:1010+1116=2126. For Sql Server is a valid operation.

  • The DATETIME values are implictly converted to INT.

    DECLARE @date1 DATETIME = ‘2010-01-20’
    , @date2 DATETIME = ‘2016-10-22’

    SELECT @date1 + @date2 AS result

    SELECT CONVERT(INT, @date1), CONVERT(INT, @date2), CONVERT(INT, @date1) + CONVERT(INT, @date2), CONVERT(DATETIME, CONVERT(INT, @date1) + CONVERT(INT, @date2))

  • SQL performed a mathematical opreration, looks like datetime was converted into a integer

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

  • because date for sql server start with 1900-01-01

  • It looks like, from testing this, that under the covers SQL is converting the DATETIMEs to INTs, adding them, then converting the result back to a DATETIME.

    I’d suspect this is an implicit conversion issue, the “+” operator forces the conversion to INTs so the @dates can be added, then converts back to DATETIME.

  • The Datetime format starts in year 1900, 110 + 116 (+month, date…) results in year 2126

  • A datetime is stored just as a numeric, as a number of days (and fractional days for the time) from an arbitrary starting date, which is just converted to date format for display.

    Now, since it seems by adding ‘2016-10-22’ to a date, we are effectively adding 116 years, one can only surmise that 0.0 refers to midnight on 1900-01-01

  • I have opinion, that example in puzzle is error. I’ve got same result using type “numeric”.


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

    declare @n1 numeric(6) = cast (@date1 as numeric(6));
    declare @n2 numeric(6) = cast (@date2 as numeric(6));

    select @n1, @n2, @n1 + @n2;

    select cast (@n1 + @n2 as datetime)

  • Dascalu Theodor
    October 18, 2017 11:00 pm

    SELECT DATEADD(DAY, CAST(@date1 AS INT)+CAST(@date2 AS INT),’1900-01-01′)

  • Samantha Miller
    October 19, 2017 10:35 pm

    Hi Pinal Dave – what was the answer to this puzzle?

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

    SELECT CAST(@date1 as int)
    SELECT CAST(@date2 as int)

    SELECT CAST(@date1 as int)+CAST(@date2 as int)
    SELECT CAST((CAST(@date1 as int)+CAST(@date2 as int)) as DATETIME)



Leave a Reply