So now that I have the latest CTP working again on my main machine, it's far less troublesome to go research things and post what I find.  Tonight I'll talk a little about datetime vs. date, as dates are on my mind for whatever reason.

So the "old" SQL Server datetime type only goes back to 1752, which seems very odd until you remember that current notions of date and time are really not that old.  In 1582, Pope Gregory XIII fixed the calendar because they realized that the year was actually slightly off compared to what the calendar said - each year the date of Easter ended up getting slightly further from what was intended.

Modern computer science students (at least ones who did the assignments *I* did in school :) will remember the funky rule for calculating a leap year - it is a leap year if the year is divisible by 4 but not 100 except for when also divisible by 400.  The year 2000 was just such a case, so we had a leap year then.  2100 will not be a leap year.  They had different fancy math in the original Julian calendar (as in Caesar) with a special month that happened every 377 years or so.  Later they tried to fix this because they wouldn't actually do the month every 377 years.  I promise - I'm not making this up.

By the time the figured it out, they were off by several days.  In modern terms, the OS shipped, everyone installed it, and it has a fatal bug that impacts every customer on upgrade to the hotfix :(.  So while today such bugs can put you out of business, back then the church had a bit more market power than the average customer might have today.  So, they decided to fix things.  So they changed the calendar and skipped 10 days in the process to fix the client.  Even worse, different clients (countries) installed the patch on different days, so they all changed dates differently.

It so happens that the British Empire (the "pink bits" on the old maps) adopted this in 1752.  That's almost 200 years after the Pope did his thing.  So, since SQL Server was first done in the US, Jan 1. 1753 is the first legal date because all of the math before that is simply dizzying.  Alaska actually didn't switch until the late 1800s since Russia controlled it before that, and the Orthodox calendar celebrates its Christmas as Easter later than those in the West because of this very issue. 

So if I try this with the old datetime type, I get:

create table datetbl(col1 datetime)
insert into datetbl(col1) values ('17510101')


Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

So I figure I'll try this out on the new date type:

create table datetbl2(col1 date)
insert into datetbl2(col1) values ('17510101')

(1 row(s) affected)

Well, the legal date range is listed as 0001 to 9999.  So let's check the math and see what happens...

create table datetbl3(col1 date, col2 date)
insert into datetbl3(col1, col2) values ('17510101', '17530101')
select DATEDIFF(dd, col1, col2) from datetbl3

731

Well, it does not appear that they skipped too many days there.  They even added a leap year since 1752 is divisible by 4 but not by 100 except for 400.  So instead of 730 we get 731.

I looked at Books online, but so far I haven't found any reference to the difference in the DATEDIFF function or in the discussion of valid ranges.  While I am sure that a number of people will not care that much and will mostly just want to use historical dates mostly with low precision, it's still important to make sure that a core system does calculations correctly.

Perhaps they will add a comment into BOL or a warning for datediff and other function uses around the various switch points for dates.  Perhaps not.  However, it's important for you, the programmer, to know the issues when dealing with old dates.

I originally learned about all of this stuff in detail when I reverse-engineered the SQL Server Expression Service Code while I was building SQL Server for Windows CE/SQL Mobile/SQL Server Compact Edition/whatever it is called now.  I've found this area to be fascinating because it takes something we take for granted and just slaps you in the face a few times.  I hope you have a bit more insight now as to the 1753 date limitation and perhaps will go crack open that history book :).

Thanks,

Conor Cunningham