SQL Server: Understanding DATEDIFF function

Question

I have difficulties understanding the function DATEDIFF. When querying

SELECT DATEDIFF(YEAR, 0, getdate())

I get difference between current year and year 1900 resulting 111. I think that the starttime should be time, not integer like 0. How 0 can be used? Why the start year in 1900, not 1753 as it should be when format is datetime?

Answer 1

It works in datetime because of implicit conversion of 0 to 1st January 1900.

Why not 1900? Why does 0 = 31 Dec 1899 for MS Access? Why are unix timestamps from 01 Jan 1970?

1753 is fairly arbitrary too: it's the major switch to the Gregorian calendar but it isn't consistent. SQL Server 2008 goes back to 01 Jan 0001 with the newer types too.