Re: A creepy story about dates. How to prevent it?

> Anyone care to run some tests to see how lax Oracle's to_date is?

I just did some checking on 9i to make sure things haven't changed since
Oracle 7, it appears they have not.

Oracle's to_date function is not lax at all. In fact, one of the
things that has always bugged me about Oracle's to_date function is
that it treats an invalid date as a SQL error, so you can't write
a SQL script that fixes legacy data. (Returning NULL for an invalid
date would help make that possible, for example.)

I think to_number does the same thing for data that doesn't conform
to the numeric model you give it.

Oracle's to_date function will only accept a month from 01-12, it will
only accept a day from 01-31 and will only accept a day which is valid
for that month, so it won't take 02/29/2001 but will take 02/29/2000.

Oracle's to_date function recognizes that 1900 was not a leap year and
2000 was. (Years that end in 00 must be divisible by 400 to be leap
years.)

I won't live to see it, but I wonder how many programs out there will
have problems with the fact that 2100 is not a leap year?

I've never had need for using the full range of dates, but according to
the documentation for Oracle 7 (the last version I have the printed
manuals for), Oracle's internal date format can store dates from
1/1/4712 BC to 12/31/4712 AD.

Oracle 7 documentation claims that the year 0 does not exist, but if
I subtract 5 days from 01/01/0001 I get 12/27/0000. (This may be
a date reporting issue rather than a date conversion/storage, though.)

I had never noticed this before, so I'm not sure whether Oracle or UNIX
handles the Julian to Gregorian calendar change 'better'.

It probably has to do with when one recognizes the calendar change. (This
sounds like something from the History Channel, doesn't it?)

'cal 1752' indicates that September 3rd through the 13th were dropped to
sync the calendar with the seasons.

However, Oracle skips 10 days in October of 1582 and treats the dates
10/05/1582 - 10/14/1582 as missing dates. Dates entered in that
range via to_date will be stored as 10/15/1582.
--
Mike Nolan