Finally, if the zero date is the start of 1900 and the dates can go up to the year 9999, why can they not go back as far using negative floats? Going back <200 years whilst being able to go forward several thousand years is a bit odd...
Tibor Karaszi touches on this subject in the book Inside SQL Server 2005 :T-SQL Programming.
In 1752 many countries that were still using the julian calendar switched over to the Gregorian calendar. If you allow dates prior to 1753 then you need to know which country you are talking about.
The following link from the book gives a more detailed explanation
http://www.karaszi.com/sqlserver/info_datetime.asp
My opinion is that restrictions are useful in many applications but there needs to be the capacity to remove them. There is nothing worse that having a datatype waving its finger at you saying "mmm..hmmm...I don't think so...".
If I want the date to say 1/1/0000 I should have some option to do so. If I want to store a time as 54:34:24.567 i would like to have the option to do so. I appreciate though that having these options may have some design challenges for the system based date and time functions.
As an example a time datatype that is not limited to 24 hours would be very handy in certain applications. To store a duration currently it is best to store a numeric value representing minutes, seconds hours etc and use the dateadd function to add this duration on top of your start time.
Formatting this numeric into the format 'hh:mm:ss' in result sets is a bit of a mess. A system datatype that could store this duration as 'hh:mm:ss.sss' without the hourly limit could alleviate some of these complications from a development perspective although may complicate things from sql server architecture perspective.
-- JP