| Author |
Topic |
|
jackstow
Posting Yak Master
160 Posts |
 Posted - 2002-03-15 : 03:53:25
|
Why is smalldatetime so small and datetime so big? for example, if I want to put the end date of a leasehold contract on a house (usually 99 years) in a table I couldn't use smalldatetime as that only goes up to 2079 - I'd have to use datetime and overshoot the date I want by 9898 years as datetime goes up to 9999 !? I guess I could use a user defined datatype but why is the difference between smalldatetime and datetime so huge - and why is there no built in mediumdatetime? Also does smalldatetime move with each new release of SQL Server? Will it always end in 2079 or 77 years in the future? Yeah, and who decided all this in the first place?Your quizzically,Jack |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-03-15 : 07:12:46
|
I suppose the answer is that they didn't want to compromise the speed by encoding both date and time into the same byte. So while the two time bytes only need to store 1440 (24*60) different values, the two date bytes need all 65536 store from 1900-01-01 to 2079-06-06. Given the speed of CPUs versus moving data, I'm not convinced this is a good argument. They could, without compromising existing data, choose to use those free 5 bits to extend it to 7641-10-21!Just don't get me started on datetime starting in 1753  Edited by - Arnold Fribble on 03/15/2002 07:16:36 |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-15 : 08:23:04
|
How do you overshoot the end date by 9900 years? Just do this:UPDATE LeasesSET ContractEndDate=DateAdd(year, 99, ContractBeginDate)Or include the DateAdd() expression when you INSERT the rows. You could do this with a trigger as well. I imagine that not all leases run 99 years, so you must be doing something to insert a fixed end date anyway.Hey Arnold, you could always use Oracle, they can handle BC/BCE dates! Let's see, today is the Ides of March, I think it was 44 BC, how many days since Julius Cesaer was stabbed? Hmmmmm.....that's a monster DateDiff() calculation  And you KNOW why 1753 is the start point: stupid Pope dropping two weeks from the calendar! |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-03-15 : 08:46:26
|
quote:
And you KNOW why 1753 is the start point: stupid Pope dropping two weeks from the calendar!
Pope Gregory XIII may have been a great man in the history of time, but even he would have had trouble doing that, since he died in 1585. In fact, he decreed that a change of 10 days be made in 1582, which was followed by many of the Catholic countries of the time.The British (and its colonies) did not adopt the Gregorian calendar until September 1752. So the choice of 1753 in a database that claims to be internationalized is quite inappropriate. |
|
|
|
jackstow
Posting Yak Master
160 Posts |
Posted - 2002-03-15 : 08:59:36
|
| Sorry, didn't make myself clear - I didn't mean the date itself would overshoot by 9898, just the use of a datatype would switch from a maximum year of 2079 to 9999 - why nothing inbetween?Also, still the question what happens to smalldatetime with release of SQL.robvolk Server.NET version 45 in 2079?? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-15 : 09:00:35
|
quote:
The British (and its colonies) did not adopt the Gregorian calendar until September 1752. So the choice of 1753 in a database that claims to be internationalized is quite inappropriate.
Yeah, those Brits always find a way to FUBAR everything for the rest of us...Maybe we should all move over to the Chinese calendar? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-15 : 09:08:17
|
quote:
Sorry, didn't make myself clear - I didn't mean the date itself would overshoot by 9898, just the use of a datatype would switch from a maximum year of 2079 to 9999 - why nothing inbetween?
It's strictly limited due to storage requirements. Like Arnold said, you have a period of 65,536 days to store in the date portion. You have to pick a start point somewhere. At least the start point for a 0 day offset is consistent between the two, which is 1/1/1900. You must admit it would be REALLY incovenient if the two date types didn't have the same baseline (just imagine the overhead of doing date arithmetic between the two types....hoooo boy!)quote:
Also, still the question what happens to smalldatetime with release of SQL.robvolk Server.NET version 45 in 2079??
I will be dead by then (or at least 110 years old and senile), so it ain't my problem!  Besides, you won't have to wait that long: Yukon will have the same datatype foundation as .Net, and it should eliminate this problem. As long as you don't try to carry over smalldatetime values for the next 78 years  |
|
|
|
jackstow
Posting Yak Master
160 Posts |
Posted - 2002-03-15 : 09:10:19
|
quote:
The British (and its colonies) did not adopt the Gregorian calendar until September 1752.
If we hadn't adopted it you wouldn't have any dates at all in the USA (except maybe "Gee Clint, the sun's rather low in the sky")- you should be thankfull!  |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-15 : 09:24:55
|
quote:
If we hadn't adopted it you wouldn't have any dates at all in the USA (except maybe "Gee Clint, the sun's rather low in the sky")- you should be thankfull!
And that differs from Stonehenge...how? How does one "adopt" a calendar anyway? Do you have to prove that you can provide and care for it? What kind of legal hoops do I have to jump through? Is there a black market for orphaned calendars from third-world countries? How much do they cost? Could always use the Mayan calendar...most accurate calendar system available until atomic clocks were invented!Man, how do these threads turn into this? |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-03-15 : 11:46:15
|
| "Le Calendrier Républicain: c'est Révolutionnaire!" |
|
|
|
|
Why is smalldatetime so small?