When I try to create a date in the year 0 I get an error:
SQL> select to_date('1-Jan-0000AD', 'dd-Mon-yyyyAD')
2 from dual
3 ;
select to_date('1-Jan-0000AD', 'dd-Mon-yyyyAD')
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
However, when I try to do date arithmetic across the year 0 I get something strange:
SQL> alter session set nls_date_format='dd-Mon-yyyyAD';
Session altered.
SQL>
SQL> select d, d+1, (d+1)-d
2 from (select to_date('31-Dec-0001BC', 'dd-Mon-yyyyAD') d
3 from dual)
4 ;
D D+1 (D+1)-D
------------- ------------- ----------
31-Dec-0001BC 01-Jan-0000AD 1
SQL>
SQL> select d, d-1, d-(d-1)
2 from (select to_date('1-Jan-0001AD', 'dd-Mon-yyyyAD') d
3 from dual)
4 ;
D D-1 D-(D-1)
------------- ------------- ----------
01-Jan-0001AD 31-Dec-0000AD 1
SQL>
SQL> select to_date('1-Jan-0001AD', 'dd-Mon-yyyyAD')
2 - to_date('31-Dec-0001BC', 'dd-Mon-yyyyAD')
3 from dual
4 ;
TO_DATE('1-JAN-0001AD','DD-MON-YYYYAD')-TO_DATE('31-DEC-0001BC','DD-MON-YYYYAD')
--------------------------------------------------------------------------------
367
Is this a bug?
Is this down to how Oracle Database calculates Julian dates.
It uses the astronomical system for these. In this, the year before 1AD is 0. So 1BC is the astronomical year 0. 2BC is -1 and so on. So Julian day 1 maps to -4712, instead of 4713 like it's supposed to:
select to_date(1, 'J') from dual;
TO_DATE(1,'J')
01-Jan-4712 BC
This leads to the inconsistency you've spotted. In Oracle Julian day 1,721,424 is 1 Jan 1 AD. But 1,721,057 is 31 Dec 1 BC. A difference of 367 days!
select to_date(1721424, 'J') ad, to_date(1721057, 'J') bc,
1721424 - 1721057 diff
from dual;
AD BC DIFF
01-Jan-0001 AD 31-Dec-0001 BC 367
This also means there are Julian values which map to the year 0, which you can't use:
select to_date(1721100, 'J') from dual;
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Bug or feature? I'll let you decide that one ;) Either way, this is unlikely to change due to the impact on legacy apps.
Further reading:
http://www.ixora.com.au/notes/julian_zero.htm http://aa.usno.navy.mil/faq/docs/millennium.php