Laurent Schneider

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> 

Put your code in <code> and </code> tags

13 Responses to “To RR or to YY ?”

1. APC Says:
   April 8th, 2008 at 12:18

   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 Says:
   April 8th, 2008 at 12:48

   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 Says:
   April 8th, 2008 at 14:37

   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. Laurent Schneider Says:
   April 8th, 2008 at 14:50

   yes, YYYY-MM-DD is pretty, it even sorts right as string

5. Gary Says:
   April 9th, 2008 at 03:03

   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.

6. Chen Shapira Says:
   April 9th, 2008 at 05:41

   FXYYYY rules indeed.
   Thanks for the insight about limitations of YYYY.

7. Laurent Schneider Says:
   April 9th, 2008 at 11:50

   dates in BC, sounds fun !

   well, truncate may reveal some inconsistencies …

   select column_value,trunc(comµçVÖå÷fÇVRÂt42r’g&öÒF&ÆR‡7—2æöF6–FFVÆ—7B†FFRs#‚ÓBÓ’rÆFFRrÓ#‚ÓBÓ’r’’°Ð¤4ôÅTÔåõdE%Tä2„4ôÀТÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÐУ’Ô"Ó‚Ô¤âÓУ’Ô"Ó‚Ô¤âÓРÐ

   :)

8. Laurent Schneider Says:
   April 9th, 2008 at 13:33

   Gary, your users will love to have in UTL_RAW.CAST_TO_NUMBER(’7E’) in the number columns

9. Chen Shapira Says:
   April 10th, 2008 at 05:40

   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 Says:
    April 10th, 2008 at 10:48

    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. Log Buffer #92: a Carnival of the Vanities for DBAs Says:
    April 11th, 2008 at 18:39

    […] Laurent Schneider is looking at the right-or-wrongness of date-format. He writes, “What is worst? To use DD-MON-RR or to use DD-MON-YY?”. Probably neither, as the comments point out. Chen Shapira says, “FXYYYY rules indeed.” […]

12. David Aldridge Says:
    April 11th, 2008 at 22:31

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

13. Laurent Schneider Says:
    April 12th, 2008 at 07:52

    yes, right :
    Oracle® Database Concepts 10g Release 2 (10.2)
    Zero and positive and negative infinity (only generated on import from Version 5 Oracle databases) are stored using unique representations. Zero and negative infinity each require 1 byte; positive infinity requires 2 bytes.

Leave a Reply