Why does the Datediff function show different values?

Question

When I am executing following query I am getting different results.

SELECT Datediff(year, 0, Getdate()); 

The result was 115

When I use this, I am getting another result:

SELECT Datediff(year, 1900, Getdate()); 

The result was 110

Actually in SQL Server it will take from 1900-01-01, but why do these show different values?

Answer 1

Try this to explain the logic:

select cast(0 as datetime) select cast(1 as datetime) 

An integer is interpreted as the number of Days since 1900-01-01 whereas a string value such as '1900' will be interpreted as a date format.

1900 Days from Jan 1st 1900 is 1905-03-16, which is five years from 1900 and 110 years from now (2015).

Answer 2

This is because if you cast 0 as datetime, it returns 1900 as the year part, whereas 1900 cast as datetime returns 1905 as the year part.

Demo

From MSDN:

Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date.

That means, casting the literal 0 to datetime is equivalent to getting the datetime value for 0 days after 1/1/1900, which is 1/1/1900. Similarly for 1900. Therefore, as @MartinSmith points out in the comments, your calculation is equivalent to SELECT Datediff(year,dateadd(d,0,'1/1/1900'), Getdate()) which returns 115 as expected.

Possibly worth noting that the MSDN page on Cast and Convert does not specifically cover this scenario i.e. int to datetime.