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
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
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
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’
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/add-transact-sql?view=sql-server-2017
(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.
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.
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.