Re: SQL Date function question

True. Just because you have '0001-01-01' in your table does not mean that
you need every date between that and early dates likely to be consumed by
your business applications.

One problem is that your table may have these rows in it:
2034-01-31
1934-01-31
which would both have, for a DateNbrYymmdd column, 340131. Which is ok
for converting a date to a numeric date yymmdd but not for doing the other
way around. Sure, there are workarounds using that with this table. It's
just something to be aware of.

As far as finding the data in the index... An index built on RealDate
will just have the real date value. It won't have the RealDate and the
DateNbrCCYYMMDD in it. Now, there's nothing stopping you from putting
both into the index. And a
select RealDate, DateNbrCCYYMMDD from ... where...
would get it from the index and not open the underlying table. And be
fast. It just messes with the minds of those Fspec oriented people who
just want to chain by RealDate and get DateNbrCCYYMMDD (or vice versa) to
see both fields in the index.
Not meant as a slam, just something to be aware of.

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".
Example an index of RealDate, DateNbrCCYYMMDD and an index of
DateNbrCCYYMMDD, RealDate to allow the index to also contain the data
used. Tow Mater may through in what if they are converting from
DateNbrCCYYMMDD to DateNbrMMDDCCYY? Sure an index on just DateNbrCCYYMMDD
may find it quickly but wouldn't an index of DateNbrCCYYMMDD,
DateNbrMMDDCCYY find all needed data in the index and be even faster? It's
easy to expand this out to extremes.


Rob Berendt