Re: NLS_DATE_FORMAT - Y2K Question

Frank Siegel wrote
> Assuming that all the PL/SQL and DML code
> always formats a 4-digit year, is there anything to "watch out" for
> when we change the NLS_DATE_FORMAT to DD-MON-YYYY?

If your code always uses 4 digits year at this very moment, then your code uses explicit to_date conversions. That's good and would even work if you changed your NLS_DATE_FORMAT to some uncommon format.

So, are you only changing NLS_DATE_FORMAT for your queries? Then remember that Oracle will alwyas order your dates right, whether or not you change NLS_DATE_FORMAT:     select my_date
    from my_table
    order by my_date;

might get you

    01-JAN-99
    31-DEC-99
    01-JAN-00
    29-FEB-00

I'd either set NLS_DATE_FORMAT to DD-MON-RRRR, DD-MON-RR or leave it at DD-MON-YY. I would never set it to use YYYY for whenever you forget to give Oracle indeed 4 digits, it will treat your year as in the very first century. Like it makes sense that Oracle prefixes a zero to the day when you enter 1-JAN-1999 instead of 01-JAN-1999, it also makes sense that Oracle prefixes two zeroes when you enter 01-JAN-99 instead of 01-JAN-1999 -- which would get you 0099...

    alter session set NLS_DATE_FORMAT='DD-MON-YYYY'     select to_char( to_date( '01-Jan-99'), 'DD-Mon-YYYY')     from dual;

So, I'd prefer DD-MON-YY (which would also get you an error whenever in your code no explicit to_date is used and a user enters 4 digits) instead of DD-MON-YYYY. Best would be RRRR, second best RR.

Arjan. Received on Fri Apr 23 1999 - 10:35:08 CDT