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.

Oleg