June 17, 2010 at 1:56 pm
nikhil0416 (6/17/2010)
... Comparing two values of the Date data type will almost always perform better than comparing an int with a date value(in which case one has to be implicitly converted and an index seek would not be performed)
Unfortunately, there won't be anything like an implicit conversion (see my example a few posts back). You'd need to write your own convertion function/sproc which would make it even worse...
June 17, 2010 at 1:57 pm
lmu92 (6/17/2010)
@stefan:I just didn't want to rule anything out. I'm constantly learning new things here.
I didn't want to sound negative against your argument. There was no intention whatsoever. If I left that impression I apologize.
Not at all. I was trying to say that I agree with you, but am always willing to hear new arguments. : -)
@David:
I guess that's about the only argument. But if you'd need to deal with dates within that range and you'd need to do any calculation based on that, it get's complicated anyway (e.g. you'd need to consider that the Gregorian calendar didn't even exist before 1582 in most catholic countries, in others up to a few hundred years later... All of a sudden the calculation of Februry 29th would depend on the country referenced. *shudder*) 😉
And I think that's one of the reasons I'd prefer a varchar. It feels less automatic to do a range selection and that'd help ensure I took when the calendar was adopted into account.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
June 17, 2010 at 2:04 pm
lmu92 (6/17/2010)
@david-2:I guess that's about the only argument. But if you'd need to deal with dates within that range and you'd need to do any calculation based on that, it get's complicated anyway (e.g. you'd need to consider that the Gregorian calendar didn't even exist before 1582 in most catholic countries, in others up to a few hundred years later... All of a sudden the calculation of Februry 29th would depend on the country referenced. *shudder*) 😉
However, even the standard datetime has that problem if you're not dealing just with England, America and other former English posessions. Russia used the Julian calendar until 1918, Greece until 1923 and Turkey until 1926, just to name a few. Then you've got the fun little problems like Alaska which used the Julian until sold to the US in 1867 and then began using the Gregorian. Not that you're likely to find many records that have this problem, but it is there.
And that's not even getting into the places that don't use either the Gregorian or the Julian calendars.
And, of course, we have our own homemade problems called "Fiscal Calendars"
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
June 17, 2010 at 2:12 pm
Correct. DATETIME is no more immune to different calendar problems than INT is. Calendar changes aren't necessarily a problem. You may not even need to do date arithmetic and even if you do it only requires that you know which calendar each date belongs to - you needn't necessarily be concerned with location or when the calendar switched. One way to do date math for historical dates is to use a single, hypothetical proleptic calendar for all dates.
June 17, 2010 at 2:19 pm
David Portas (6/17/2010)
Correct. DATETIME is no more immune to different calendar problems than INT is. Calendar changes aren't necessarily a problem. You may not even need to do date arithmetic and even if you do it only requires that you know which calendar each date belongs to - you needn't necessarily be concerned with location or when the calendar switched. One way to do date math for historical dates is to use a single, hypothetical proleptic calendar for all dates.
I see the main focus for historical dates as being aware of your source. If you're using this "single, hypothetical proleptic calendar", you have to know before you do the entry what system the authors were using. Once it is in there, you have no problems with calculations. However, if you're going to output any dates based on these calculations instead of just showing durations, you have to be aware of what calendar system the users will be expecting. This will often need to take into account the system the original date was using so the duration will make sense.
Most English-language history will use the British changeover, but if you're using a Russian source for something that happened in the 1800s you still need to be aware of the change so your durations aren't off when you report back to the user.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
June 17, 2010 at 2:21 pm
Stefan Krzywicki (6/17/2010)
However, even the standard datetime has that problem if you're not dealing just with England, America and other former English posessions. Russia used the Julian calendar until 1918, Greece until 1923 and Turkey until 1926, just to name a few. Then you've got the fun little problems like Alaska which used the Julian until sold to the US in 1867 and then began using the Gregorian. Not that you're likely to find many records that have this problem, but it is there.
And that's not even getting into the places that don't use either the Gregorian or the Julian calendars.
And, of course, we have our own homemade problems called "Fiscal Calendars"
I herewith change my emotion from *shudder* to *scared* 🙂
I don't even want to think about how many databases are out there failing a test against proper use of calendar functions. Especially the ones you named since some of those most probably will have people born in the "wrong calendar age"...
June 17, 2010 at 2:25 pm
lmu92 (6/17/2010)
I herewith change my emotion from *shudder* to *scared* 🙂I don't even want to think about how many databases are out there failing a test against proper use of calendar functions. Especially the ones you named since some of those most probably will have people born in the "wrong calendar age"...
Well, it IS becoming less and less of a problem as the years go by. But there are people I work with who were born in countries with different calendar systems. They converted the dates before they came to the States, but if you wanted to use their original birthdate in their original calendar...
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply