Sunday, March 04, 2012

Bug 106242

There was no year 0 in the calendar and Oracle's to_date function recognises this:

SQL> alter session set
  2  nls_date_format = 'DD-MON-YYYY BC'
  3  /
 
Session altered.
 
SQL> select to_date('01-JAN-0000 AD','DD-MON-YYYY AD') from dual
  2  /
select to_date('01-JAN-0000 AD','DD-MON-YYYY AD') from dual
               *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and
+9999, and not be 0
 
SQL>

However, Oracle's algorithm for date arithmetic works differently. I believe this is bug 106242 but My Oracle Support does not make it clear. The example below was tested on Oracle 11. First display 31st December 1 BC:

SQL> select
  2  to_date('31-DEC-0001 BC', 'DD-MON-YYYY BC')
  3  from dual
  4  /
 
TO_DATE('31-DE
--------------
31-DEC-0001 BC
 
SQL>

Display the next day. This should be 1st January 1 AD (as there was no year 0) but it isn't:

SQL> select
  2  to_date('31-DEC-0001 BC', 'DD-MON-YYYY BC') + 1
  3  from dual
 4  /
 
TO_DATE('31-DE
--------------
01-JAN-0000 AD
 
SQL>

No comments: