Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Tough Date Question
- Thread starter sguslan
- Start date
tlbroadbent
MIS
Sorry. Datetime is limited to dates after 1752. You'll have to use another data type to store earlier dates. Char or Varchar is probably the best choice. You'll have to create your own date calcualtion functions because the built-in SQL functions will not work on dates earlier that Jan 1 1753. Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
You might be able to take advantage of the fact that dates can go as high as 9999. That gives you a lot of years to work with, most of which you will never need.
In the Gregorian calendar, as I understand it, there is a repeating cycle every 2800 years. That means that in 4802 the calendar will be exactly the same as in 2002.
If you are willing to go to the trouble, you can store dates as (actual date + 2800 years). Then you can do calculations based on SQL Server date functions. Of course whenever you display a date, you will have to apply logic that subtracts 2800 years from the date that would otherwise appear.
I wouldn't bet that this is the easiest way to do what you want, but it may be worth a try. As always there's a trade off. In this case you would get to use SQL Server date functions at the cost of having to calculate the 2800 year offset every time a date is entered or displayed.
In the Gregorian calendar, as I understand it, there is a repeating cycle every 2800 years. That means that in 4802 the calendar will be exactly the same as in 2002.
If you are willing to go to the trouble, you can store dates as (actual date + 2800 years). Then you can do calculations based on SQL Server date functions. Of course whenever you display a date, you will have to apply logic that subtracts 2800 years from the date that would otherwise appear.
I wouldn't bet that this is the easiest way to do what you want, but it may be worth a try. As always there's a trade off. In this case you would get to use SQL Server date functions at the cost of having to calculate the 2800 year offset every time a date is entered or displayed.
- Thread starter
- #5
Thanx Essa, Terry and Kral.
I read some articles at discussing the historical reason behind this. In short before Sep 2 1752 there was a problem with date calculations that made the year[shorter or longer], so the British Monarch decided to fix the problem and made 1752 12-days shorter.
It makes sense to me, but I worked with Access,DB2 and Oracle, all of which accept dates before 1/1/1753. Does that mean those DB systems DO NOT take care about the Gregorian calendar problems befor 1753?
I am thinking seriuosly now to store data on Oracle, though I hate it so much!
I read some articles at discussing the historical reason behind this. In short before Sep 2 1752 there was a problem with date calculations that made the year[shorter or longer], so the British Monarch decided to fix the problem and made 1752 12-days shorter.
It makes sense to me, but I worked with Access,DB2 and Oracle, all of which accept dates before 1/1/1753. Does that mean those DB systems DO NOT take care about the Gregorian calendar problems befor 1753?
I am thinking seriuosly now to store data on Oracle, though I hate it so much!
- Thread starter
- #6
Sorry, forgot to include the URL to the article.
There is admittedly some logic behind SQL Server's decision to disallow dates before 1753. There are a lot of complexities in allowing dates before the adoption of the Gregorian calendar. As you are finding out, however, avoiding the problem doesn't make it go away. The net result is that the datetime datatype has limited functionality, and application developers are forced to invent their own workarounds.
By the way, that 1753 limit is a little dubious even if you accept the Microsoft rationale. Not all countries adopted the Gregorian calendar at the same time. Czarist Russia never adopted it at all. It wasn't until the communists took over that the calendar changed. Because of the difference, I've read that the "October Revolution" actually happened in November.
My guess is that some future enhancement will introduce a date datatype with a wider range.
By the way, that 1753 limit is a little dubious even if you accept the Microsoft rationale. Not all countries adopted the Gregorian calendar at the same time. Czarist Russia never adopted it at all. It wasn't until the communists took over that the calendar changed. Because of the difference, I've read that the "October Revolution" actually happened in November.
My guess is that some future enhancement will introduce a date datatype with a wider range.
tlbroadbent
MIS
It is not just a Microsoft rationale. Sybase had the same date limit before Microsoft got into the SQL Server business. You may or may not know that SQL Server was originally Sybase.
Kalen Delaney gave the following explanation in SQL Magazine. [ul]The reason for the early cutoff of possible datetime values isn't mathematical but historical. In September 1752, Great Britain adopted the Gregorian calendar, which differed from the one previously used in Great Britain and its colonies by 12 days. The change happened on September 2, 1752, so the next day was September 14, 1752.
So, with 12 days lost, how can you compute dates? For example, how can you compute the number of days between October 12, 1492, and July 4, 1776? Do you include those missing 12 days? To avoid having to solve this problem, the original Sybase SQL Server developers decided not to allow dates before 1753. You can store earlier dates by using character fields, but you can't use any datetime functions with the earlier dates that you store in character fields. ([/ul]I understand that UNIX handles the dates by taking into account the missing 12 days and the 1752 change to the Gregorian calendar in England and its colonies.
If you are interested in using a more universal calendar, consider Julian. "Julian dates (abbreviated JD) are simply a continuous count of days and fractions since noon Universal Time on January 1, 4713 BCE (on the Julian calendar). Almost 2.5 million days have transpired since this date. BCE is "before common era." See the complete explanation at ...
Julian Date Converter
Converting Between Julian Dates and
Gregorian Calendar Dates
Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
Kalen Delaney gave the following explanation in SQL Magazine. [ul]The reason for the early cutoff of possible datetime values isn't mathematical but historical. In September 1752, Great Britain adopted the Gregorian calendar, which differed from the one previously used in Great Britain and its colonies by 12 days. The change happened on September 2, 1752, so the next day was September 14, 1752.
So, with 12 days lost, how can you compute dates? For example, how can you compute the number of days between October 12, 1492, and July 4, 1776? Do you include those missing 12 days? To avoid having to solve this problem, the original Sybase SQL Server developers decided not to allow dates before 1753. You can store earlier dates by using character fields, but you can't use any datetime functions with the earlier dates that you store in character fields. ([/ul]I understand that UNIX handles the dates by taking into account the missing 12 days and the 1752 change to the Gregorian calendar in England and its colonies.
If you are interested in using a more universal calendar, consider Julian. "Julian dates (abbreviated JD) are simply a continuous count of days and fractions since noon Universal Time on January 1, 4713 BCE (on the Julian calendar). Almost 2.5 million days have transpired since this date. BCE is "before common era." See the complete explanation at ...
Julian Date Converter
Converting Between Julian Dates and
Gregorian Calendar Dates
Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
- Thread starter
- #9
tlbroadbent
MIS
I'm afraid you'll have to create your own functions. I've not done this myself but found the following links with sample or downloadable code.
Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
- Thread starter
- #11
Cheers Terry..
Thanx for the great support everyone gets from you. I am reasearching storing dates as Julian counts in numeric format and writing sql2000 UDFunctions to convert back and forth.
I tired Oracle it works OK, but still I do not like it.
Thanx again..
Thanx for the great support everyone gets from you. I am reasearching storing dates as Julian counts in numeric format and writing sql2000 UDFunctions to convert back and forth.
I tired Oracle it works OK, but still I do not like it.
Thanx again..
- Status
Similar threads
- Locked
- Question