Andrew Fraser DBA

Oracle range limit AD BC dates

This is the extreme limit for oracle dates:

alter session set nls_date_format = 'Dy DD-Mon-YYYY AD' ;
select sysdate, to_date('01-JAN-4712 BC','DD-MON-YYYY AD'), to_date('30-DEC-9999 AD','DD-MON-YYYY AD') from dual ;

Without encountering this error:

ORA-01841: (full) year must be between -4713 and +9999, and not be 0
01841. 00000 – “(full) year must be between -4713 and +9999, and not be 0”
*Cause: Illegal year entered
*Action: Input year in the specified range