by Michael S. Kaplan, published on 2010/03/21 07:01 -04:00, original URI: http://blogs.msdn.com/b/michkap/archive/2010/03/21/9980256.aspx
Over in the Suggestion Box, PerryN Newton asked:
Michael,
Your blogs are very interesting reads and very informative - thanks!
Observation - you seem very informed on calendars and date functions, and have written several posts relating to them.
Question: Have you ever tried to import Visual FoxPro table data that simply lists U.S. Presidents and their birthdates into SQL-Server?
Suggestion: When I last tried this, using VFP 6.0 and SQL-Server 6.0 I would always get an error and discovered it was a limitation of the beginning date recognized in SQL-Server and I wondered if this limit has been since documented (KB) or modified by subsequent SQL-Server versions.
Thanks for all you do and share!
Excellent question!
Now if you look at the latest SQl Server docs, you will get hints as to what might have been going on here originally.
Specifically, the table comparing the relevant datatypes in Date and Time Data Types and Functions (Transact-SQL):
Date and Time Data TypesThe Transact-SQL date and time data types are listed in the following table.
Data type Format Range Accuracy Storage size (bytes) User-defined fractional second precision Time zone offset hh:mm:ss[.nnnnnnn]
00:00:00.0000000 through 23:59:59.9999999
100 nanoseconds
3 to 5
Yes
No
YYYY-MM-DD
0001-01-01 through 9999-12-31
1 day
3
No
No
YYYY-MM-DD hh:mm:ss
1900-01-01 through 2079-06-06
1 minute
4
No
No
YYYY-MM-DD hh:mm:ss[.nnn]
1753-01-01 through 9999-12-31
0.00333 second
8
No
No
YYYY-MM-DD hh:mm:ss[.nnnnnnn]
0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999
100 nanoseconds
6 to 8
Yes
No
YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm
0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC)
100 nanoseconds
8 to 10
Yes
Yes
Now obviously there are some datatypes that would not handle the full range of presidents, and others that have way more detail than one might need.
But one can find one or two that are just right!
Mike on 21 Mar 2010 8:19 AM:
Ah, but how do we handle the fact that George Washington (and John Adams, and Thomas Jefferson, ...) was born before 1753, the first full year that the colonies were on the Gregorian calendar?
Obviously we could give their birthdates in terms of the proleptic Gregorian calendar, and the various doc pages you link to seem to suggest this convention. But that just brings up another question - how do we support finding Washington under both 1732 (new calendar) and 1731 (old calendar)? Or do we even support that at all? You could go mad just trying to keep all these things straight in your code!
Michael S. Kaplan on 21 Mar 2010 9:24 AM:
Software products tend to ignore the whole issue, actually -- and talk about a Gregorian calendar date in 100 AD as if such a thing ever existed. SQL Server is hardly the worst of the offenders on that score, though -- pretty much all software sucks in that regard, on all platforms.