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.
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-
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-
- SQL SERVER – Puzzle – Shortest Code to Produce the Number 1000000000 (One Billion)
- SQL SERVER – Select Unique Data From a Column Exist in Two Different Table
- SQL SERVER – Puzzle – Write a Shortest Code to Produce Zero
Reference: Pinal Dave (https://blog.sqlauthority.com)
29 Comments. Leave new
‘+’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
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
DECLARE @date1 DATETIME, @date2 DATETIME
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)
result
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
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
DECLARE @date1 DATETIME, @date2 DATETIME
SELECT @date1=’2010-01-20′, @date2=’2016-10-22′
SELECT CONVERT(DATETIME, CONVERT(INT, @date1) + CONVERT(INT, @date2))
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”.
DECLARE @date1 DATETIME, @date2 DATETIME;
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)
SELECT DATEADD(DAY, CAST(@date1 AS INT)+CAST(@date2 AS INT),’1900-01-01′)
Hi Pinal Dave – what was the answer to this puzzle?
I will post it on Monday.
DECLARE @date1 DATETIME, @date2 DATETIME
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)
SELECT CAST(0 as DATETIME)