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. 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. 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. 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;

  4. 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.

  5. 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
    
    

    :)

  6. 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!

  7. dear, it is not inf, it is nan :D

    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 :twisted:

    select cast((-utl_raw.cast_to_number('7E')) as binary_double) from dual;
    
    CAST((-UTL_RAW.CAST_TO_NUMBER('7E'))ASBINARY_DOUBLE)
    ----------------------------------------------------
                                                     Inf
    

  8. 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).

  9. 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?

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>