ORA-01841

There was no year 0 in the calendar and Oracle's to_date function recognises this but for some reason it rejects -4713. If anybody knows why, perhaps they could add a comment below: 

SQL> select
  2  to_char(to_date('-4714','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /
to_char(to_date('-4714','SYYYY'),'YYYY BC')
                *
ERROR at line 2:
ORA-01841: (full) year must be between -4713 and
+9999, and not be 0

SQL> select
  2  to_char(to_date('-4713','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /
to_char(to_date('-4713','SYYYY'),'YYYY BC')
                *
ERROR at line 2:
ORA-01841: (full) year must be between -4713 and
+9999, and not be 0

SQL> select
  2  to_char(to_date('-4712','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /

VALID_YEAR
----------
4712 BC

SQL> select
  2  to_char(to_date('-1','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /

VALID_YEAR
----------
0001 BC

SQL> select
  2  to_char(to_date('-0','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /
to_char(to_date('-0','SYYYY'),'YYYY BC')
                *
ERROR at line 2:
ORA-01841: (full) year must be between -4713 and
+9999, and not be 0

SQL> select
  2  to_char(to_date('0','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /
to_char(to_date('0','SYYYY'),'YYYY BC')
                *
ERROR at line 2:
ORA-01841: (full) year must be between -4713 and
+9999, and not be 0

SQL> select
  2  to_char(to_date('+0','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /
to_char(to_date('+0','SYYYY'),'YYYY BC')
                *
ERROR at line 2:
ORA-01841: (full) year must be between -4713 and
+9999, and not be 0

SQL> select
  2  to_char(to_date('1','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /

VALID_YEAR
----------
0001 AD

SQL> select
  2  to_char(to_date('9999','SYYYY'),'YYYY BC')
  3  valid_year from dual
  4  /

VALID_YEAR
----------
9999 AD

SQL>

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>