Re: Which Mask is Better: YYYY or RRRR?

dhmac wrote
>Which is the best mask to use for NLS_DATE_FORMAT
> DD-MON-YYYY or DD-MON-RRRR?

I'd use RRRR. This accepts both 1890 for 1890, 1999 and 99 for 1999, or 2049 and 49 for 2049. However, in my GUIs etc. I'd *force* the user to enter 4 digits for the year, so the NLS_DATE_FORMAT would only be used as a backup.

Setting RRRR as the default format prevents you from getting years like 0099. Please try:

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

to get some wrong dates. Or even get an exception using:

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

So: I'd use RRRR. If for some reason that was not allowed (for example: queries are expected to print only two digits if no to_char is used -- which is not a Y2K problem), then I'd use RR.

In fact, I think I'd prefer YY over YYYY! Or even something non-usable like 'Year YYYY month MONTH day DD' to prevent developers from actually depending on the NLS_DATE_FORMAT and force an error whenever no specific date format is specified! Chances that a date fits into such stupid format are low.

Arjan. Received on Thu Mar 04 1999 - 00:14:25 CST