Oracle Internals Notes

Julian days and year 0 (bug)

The Julian day for a date is the number of days since 01-JAN-4713 BC inclusive. That date was chosen as the start of the "Julian period" by Joseph Scaliger in the sixteenth century because several long term calendar patterns coincided in that year (details). Oracle uses Julian days internally for date arithmetic. For example, when adding a number to a date, Oracle first converts the date to a Julian day, then performs the addition, and then converts the resulting Julian day back into a date.

The problem is that there was no year 0, and while the validation for Oracle's DATE datatype rejects dates in the year 0, its Julian date algorithm allows for year 0. So although the day after 31-DEC-0001 BC was 01-JAN-0001 AD, Oracle thinks that there were 366 other days in between and can even generate non-existent dates in that year.

SQL> alter session set nls_date_format = 'DD-MON-YYYY BC';

Session altered.

SQL> select to_date('01-JAN-0001 AD') - to_date('31-DEC-0001 BC') from dual; 

TO_DATE('01-JAN-0001AD')-TO_DATE('31-DEC-0001BC')
-------------------------------------------------
                                              367

SQL> select to_date('31-DEC-0001 BC')+1 from dual;

TO_DATE('31-DE
--------------
01-JAN-0000 AD

The point at which you need to be most wary of this bug (106242) is when using Julian day functionality explicitly. For example, some applications make use of this functionality to convert numbers to words for purposes such as printing cheques. Unfortunately, this fails with an unexpected ORA-1841 error if the number happens to fall into the range of Julian days that Oracle maps into year 0.

SQL> select to_char(to_date(1234, 'J'), 'JSP') from dual;

TO_CHAR(TO_DATE(1234,'J'),'JSP')
------------------------------------
ONE THOUSAND TWO HUNDRED THIRTY-FOUR

SQL> select to_date(1721058, 'J') from dual;
select to_date(1721058, 'J') from dual
               *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Other effects of this bug are that the Julian days for all BC dates are incorrect, and that despite that the ORA-01841 error message correctly allows for dates back to the start of the Julian period, it is not in fact possible to enter dates in 4713 BC because Oracle's Julian day 1 falls on 01-JAN-4712 BC.

SQL> select to_char(to_date('01-JAN-4712 BC'), 'J') from dual;

TO_CHAR
-------
0000001

SQL> select to_char(to_date('31-DEC-4713 BC'), 'J') from dual;
select to_char(to_date('31-DEC-4713 BC'), 'J') from dual
                       *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Oracle are aware of this bug but are reluctant to fix it because of potential backwards compatibility difficulties. Instead it is just documented that Oracle's Julian period begins in 4712 BC, and that "Oracle Julian dates might not be compatible with Julian dates generated by other date algorithms".