Good question. The difference between DATE and DATETIME in SQL Server 2008 is important. Thanks.
The answer 40147 looked familiar to me. It is what I have known to be called a "hundred-year" format date. I have seen it used on the IBM i. 40147 is that format equivalent to 2009-12-01. It's not really important. I just found it interesting.
The functionality to allow conversion of int to datetime exists in SQL Server 2005/2008 and it is identical. Datetime needs 8 bytes of storage (2 integers). One int (first 4 bytes) is used to store the number of days from the zero datetime ('1900-01-01') and the other to store the number of ticks from midnight. This is why you can add number to an instance of a date.
select cast(40147 as datetime); -- displays 2009-12-02 00:00:00.000
select cast(-53690 as datetime); -- displays smallest datetime value '1753-01-01'
select cast(getDate() as int); -- displays 40294
select getDate() + 1; -- displays tomorrows date same time
The reason the latter works (or why the addition of int to a datetime works) is because 1
is first implicitly converted to datetime ('Dec 2 1900'
) and +
operator is legal with datetime values. So to the database engine the statement
select getDate() + 1;
is identical to
select getDate() + cast(1 as datetime); -- 1 as datetime is '1900-01-02'
is identical to
select getDate() + cast('1900-01-02' as datetime);
This is the reason it works (All of the above assumes En-US format yyyy-mm-dd).
It looks like with date data type in SQL Server 2008 the situation is different because date needs 3 bytes of storage and therefore it would be stupid for the database team to allow implementation of addition of a 4 byte int to the 3 byte struct. Thus, they don't allow it raising operand clash exception.