Re: Date Query y2k

attwoody wrote:

> My first suggestion is to check your NLS_DATE_FORMAT in the
> database - I just spent the worst week of my life having to
> recreate a database to get a table back (it's a long story)
> because the NLS_DATE_FORMAT was set to 'DD-MON-YY' instead of
> either 'DD-MON-RR' or 'DD-MON-YYYY'.
>
> NLS_DATE_FORMAT can be set for a session, or permanently
> set in the init.ora.
>
> On January 13, 2000, I executed this query:
>
> delete from name-of-table
> where date-on-table < '03-JUL-99';
>

Ouch!

> What I _wanted_ was to purge all the records earlier than
> July 3, 1999; what happened was that EVERYTHING was deleted
> because '13-JAN-00' was interpreted as being less than
> '03-JUL-99'; ie, as a date in 1900 and not 2000.
>

No, it was because '03-JUL-99' was interpreted as 03-JUL-2099. YY is the 2-digit year in the current century.

> When I executed a similar query in 1999, everything was
> just fine.

Of course, because then '03-JUL-99' was interpreted as 03-JUL-1999.

Funny, isn't it, how most people worried that 00 will be interpreted correctly, but few thought about what would happen to 99, 98, 97 etc. I bet there are quite a few small to midsize shops who ran into similar problems, based on the human y2k issue of intuitively interpreting, and using 99, 98 etc. as if in the RR format.

To the original poster I'd say, your queries and format strings look ok, and should work (no matter what your NLS_DATE_FORMAT is set to btw.), which leaves your data: Are you 100% sure the data in your date columns is ok? No dates in 2099 in there? or in 0099 maybe? Maybe there is an old sqlloader control file somewhere on your system loading dates as dd-mon-yy (worked great last year).

Christian Received on Sat Jan 22 2000 - 19:20:56 CST