Re: SQL Date function question

On 08-Jul-2011 13:03 , rob@xxxxxxxxx wrote:

Then there's what indexes again. Captain Obvious may say an index by
every column should cover everything they want to convert from. With
perhaps some combo indexes for stuff like combined fields (year,
month, day as separate fields). Captain Database may suggest twice
that so you have every combo of "from RealDate" and every combo of
"to RealDate". <<SNIP some craziness ensues to a point where...>>
It's easy to expand this out to extremes.

Having application-specific calendar files and having a narrowed focus [for formats\elements] and the years-covered are both important. Even a full 100-year calendar supporting two or three common formats and a couple different date-elements would probably take up very little storage even when duplicated several times in indexes; 36K rows is not much, and in many cases maybe only one tenth might be required. Most applications and even across applications, I typically saw just one to three non-date date variants; in the US mostly DDMMYY and then some pseudo corrections to YYMMDD or the YYYYMMDD, and most always as a decimal vs character type. Of course an end-all be-all table of wildly varied formats across all dates in most every year including flags of any whimsical nature would probably be unwieldy... but also surely unnecessary, as would creating just about every permutation of index without the query engine making some recommendations. :-) For example, a grandiose calendar need not exist to be able to combine the warehouse inventory with a personnel file which may have very divergent needs for calendar dates, just to enable some report that runs every quarter to take advantage of that calendar concept. Sometimes there is just no sense in trying to carry an idea to the extreme. ;-)

Regards, Chuck