To RR or to YY ?

What is worst? To use DD-MON-RR or to use DD-MON-YY? When entering the birthday of my grandfather, I will write it as 31-JUL-1912, so both formats will be fine. If I enter it 31-JUL-12, both formats will be wrong. Ok, which date will I enter now and in the future? For short-time contracts I will enter dates like 01-AUG-08 or 31-DEC-11, both formats will be fine. For long time contracts like retirement saving plan I will enter 31-MAR-36, which is still fine, because I am old enough :mrgreen: Juniors in my company will enter dates like 30-JUN-52, which will be fine with YY and wrong with RR or RRRR.

Ok, what is the recommended format then?

DD-MM-FXYYYY is probably fine. FX forces you to enter 4-digits years.


SQL> select
2 to_date('31-JUL-1912','DD-MON-YY') YY1912,
3 to_date('31-JUL-12','DD-MON-YY') YY12,
4 to_date('31-JUL-12','DD-MON-RR') RR12
5 from dual;

YY1912 YY12 RR12
---------- ---------- ----------
1912-07-31 2012-07-31 2012-07-31

SQL> select
2 to_date('31-MAR-2036','DD-MON-YY') YY2036,
3 to_date('31-MAR-36','DD-MON-YY') YY36,
4 to_date('31-MAR-36','DD-MON-RR') RR36
5 from dual;

YY2036 YY36 RR36
---------- ---------- ----------
2036-03-31 2036-03-31 2036-03-31

SQL> select
2 to_date('30-JUN-2052','DD-MON-YY') YY2052,
3 to_date('30-JUN-52','DD-MON-YY') YY52,
4 to_date('30-JUN-52','DD-MON-RR') RR52
5 from dual;

YY2052 YY52 RR52
---------- ---------- ----------
2052-06-30 2052-06-30 1952-06-30

SQL> select
2 to_date('8/4/2008','DD-MM-FXYYYY') FXYYYY2008
3 from dual;

FXYYYY2008
----------
2008-04-08

SQL> select
2 to_date('08-04-08','DD-MM-FXYYYY') FXYYYY08
3 from dual;
to_date('08-04-08','DD-MM-FXYYYY') FXYYYY08
*
ERROR at line 2:
ORA-01862: the numeric value does not match the length
of the format item

SQL>

16 thoughts on “To RR or to YY ?”

  1. APC

    RR – or even RRRR – is a bug waiting to happen. It should never be used in new code and certainly not as a mask for data input.

    Cheers, APC

  2. Laurent Schneider Post author

    Yes, but YYYY is not better than RRRR. Only FXYYYY rules

    SQL> select to_date('08-APR-08','DD-MON-YYYY') YYYY from dual;

    YYYY
    ----------
    0008-04-08

    SQL> select to_date('08-APR-08','DD-MON-RRRR') RRRR from dual;

    RRRR
    ----------
    2008-04-08

    SQL> select to_date('08-APR-08','DD-MON-FXYYYY') YYYY from dual;
    select to_date('08-APR-08','DD-MON-FXYYYY') YYYY from dual
    *
    ERROR at line 1:
    ORA-01862: the numeric value does not match the length of the format item

  3. David Aldridge

    FXYYYY … very interesting.

    Despite ten years of living in the US my mind still rebels from the default local data ordering of MM/DD/YYYY, which is just terribly wrong. Even the natives find it indefensible. Because my native DD/MM/YYYY confuses my co-workers I now use the ISO format, thus upsetting everyone equally …

    select date ‘2007-01-25’ from dual;

  5. Gary

    select date ’08-12-31′ from dual; gives the year 0008, so FXYYYY still wins out.
    If you really want to confuse co-workers, switch a bunch of dates from AD to BC. They’ll never work it out. Why Oracle (or SQL) thought it would ever be necessary to store an exact date (and time) two thousand plus years ago is stunning. Who thought that would ever be useful ?
    Please, give me a setting so that database-wide, Oracle would reject dates outside sensible boundaries. Default it to 1-jan-1800 to 31-dec-2199 and 99% of the date problems would disappear.

  7. Laurent Schneider Post author

    dates in BC, sounds fun !

    well, truncate may reveal some inconsistencies …

    select column_value,trunc(column_value,'CC') from table(sys.odcidatelist(date '2008-04-09',date '-2008-04-09')) ;
    COLUMN_VA TRUNC(COL
    --------- ---------
    09-APR-08 01-JAN-01
    09-APR-08 01-JAN-00

    🙂

  9. Chen Shapira

    Hey Laurent,

    Forget about Gary’s users. I want an explanation for 7E as number.

    It displays as -inf in sqlplus, but it clearly doesn’t behave as -inf:

    SQL> select UTL_RAW.CAST_TO_NUMBER(‘7E’) from dual;

    UTL_RAW.CAST_TO_NUMBER(‘7E’)
    —————————-
    -~

    SQL> select UTL_RAW.CAST_TO_NUMBER(‘7E’)+1 from dual;

    UTL_RAW.CAST_TO_NUMBER(‘7E’)+1
    ——————————
    1

    Other single byte numbers also display the same:

    SQL> select UTL_RAW.CAST_TO_NUMBER(‘7F’) from dual;

    UTL_RAW.CAST_TO_NUMBER(‘7F’)
    —————————-
    -~

    We can also get inf:

    SQL> select UTL_RAW.CAST_TO_NUMBER(’81’) from dual

    UTL_RAW.CAST_TO_NUMBER(’81’)
    —————————-
    ~

    And of course, 0 is between 🙂 (but this is trivial)

    SQL> select UTL_RAW.CAST_TO_NUMBER(’80’) from dual

    UTL_RAW.CAST_TO_NUMBER(’80’)
    —————————-
    0

    Steve Adams wrote some stuff about infinite numbers:
    http://www.ixora.com.au/notes/infinity.htm
    but I suspect that this is unrelated.

    Please explain!

  10. Laurent Schneider Post author

    dear, it is not inf, it is nan 😀

    select cast(utl_raw.cast_to_number('7E') as binary_double) from dual;

    CAST(UTL_RAW.CAST_TO_NUMBER('7E')ASBINARY_DOUBLE)
    -------------------------------------------------
    NAN

    as NaN does not exist as a Number, the only purpose to have such a number is annoying your colleagues. Of course it is not supported. And operations involving such an unsupported thing are, well, surprising 😈

    select cast((-utl_raw.cast_to_number('7E')) as binary_double) from dual;

    CAST((-UTL_RAW.CAST_TO_NUMBER('7E'))ASBINARY_DOUBLE)
    ----------------------------------------------------
    Inf

  11. Pingback: Log Buffer #92: a Carnival of the Vanities for DBAs

  12. David Aldridge

    Wasn’t there plus and minus infinity in version up to up to Oracle v5? I think I rememeber _reading_ about that. (I’m not that old).

  14. Khalid

    What about this:

    select to_date(‘2008′,’yyyy’) from dual;
    The query returns the first day of the current month instead of returning the first day of the year.

    Any explanation for such behaviour?

  16. Pingback: Log Buffer #92: A Carnival of the Vanities for DBAs

Comments are closed.