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>