• mtassin (11/5/2010)


    For instance, you can cast -1 as a datetime. You'll get 1899-12-31.

    This can go back to 1753-01-01, but not to 1752-12-31.

    And *that* is because of the change from the Julian calendar (introduced by Julias Ceasar in 45BC; one leap day every four years, with no exception, for a year length of exactly 365.25 days) to the Gregorian calendar (introduced by Pope Gregory XIII; one leap day every four years, no leap day every 100 years, except every 400 years, for a year length of exactly 365.2425 days). In order to correct the error that had build between 45BC and the moment the switch was made, several days had to be skipped. But the transition was made at different days in different countries.

    For example, Spain adopted the new calendar in 1582. The error was 9 days by then, so they skipped from Julian Thursday, 4 October 1582 to Gregorian Thursday, 5 October 1583. Greece, on the other hand, waited until 1923 - the amount of days to correct was 13 by then, so they went from Wednesday, 15 February 1923 to Thursday, 1 March 1923.

    Britain, the British Empire, and their possessions (a.o. the Eastern part of what now is the United States) switched in 1752 - Wednesday, 2 September 1752 was followed by Thursday, 14 September 1752. So it looks as if the choice to cut off the valid range for datetime at 1753 is inspired by a very Anglosaxan-centric idea of how to prevent extra complexity to this data type.

    See http://en.wikipedia.org/wiki/Gregorian_calendar for much more useless information on this fascinating subject 😉

    Edit: fixed the hyperlink


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/