How about a date, baby?

Laurent Schneider has posted another interesting insight into the limits of Oracle. I always thought that the highest date we could have in Oracle was 31-DEC-9999. Well that's certainly the highest date we can fit into the standard NLS date format. But the date buffer will actually go up to Friday 1st January 15402 A.D. Read Laurent's post to see how he does it.

Incidentally, you will probably need to tweak your NLS settings to see the results:

SQL> alter session set nls_date_format='dd-mon-yyyy';

Session altered.

SQL> select round(date '9999-01-01','CC') from dual;
ERROR:
ORA-01801: date format is too long for internal buffer



no rows selected

SQL> alter session set nls_date_format='FMDay ddth Month YYYY B.C.';

Session altered.

SQL>  select round(date '9999-01-01','CC') from dual;
ROUND(DATE'9999-01-01','CC')
--------------------------------
Monday 1st January 10001 AD

SQL> 

Incidentally, the Julian dates break down at the outer limits...

SQL> select to_char(round(date '9999-01-01','CC'), 'J') from dual;
TO_CHAR
-------
0000000


SQL> select to_char(trunc(date '-4712-1-1','CC'), 'J') from dual;
TO_CHAR
-------
0000000

SQL> 

... and without resorting to RAW twiddling, 1st January 4800 BC is the furthest back our time machine will go....

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

SQL>