Jerry Nixon on Windows: The earliest date held by Sql Server

Jerry Nixon on Windows

Tuesday, March 1, 2005

The earliest date held by Sql Server

The earliest date held by Sql Server

I knew January 1, 1753 was the smallest date supported by the SQL Datetime data type and now I know why. It's not that I have always wondered why, but it is interesting to know if anyone ever asks.

1753 is the earliest year accepted by Sql Server. There are two calendar types in the western world. The Julian and the Gregorian calendars. These calendars did not match up by x number of days, depending on the year you are comparing. Eventually, countries started switching from the Julian calendar to the Gregorian calendar, which meant during the switch, they would have to skip forward anywhere from 10-13 days. Italy, Poland, Portugal, and Spain made this switch way back in 1582 and advanced 10 days forward. Great Britian made the switch in 1782, advancing 12 days forward. Albania switched in 1912, advancing 13 days forward. In the United States, depending on which part of the country you were in, you changed calendars anywhere from 1582 to 1867. Since most parts of the world changed calendars prior to 1753, this was chosen as the cut off date in Sql Server/Sybase. In order to calculate an actual date any earlier than that, you would have to go through this conversion process that would require you to know not only the country in which you are converting the date for, but also what year they did their conversion to the Gregorian calendar.