Friday, January 25, 2008

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>

Labels: ,

2 Comments:

Blogger Laurent Schneider said...

spell the year and go further to 15655 ;-)

SQL> alter session set nls_date_format='MONTH SYYYYSP';

Session altered.

SQL> exec declare dt date; begin dbms_stats.convert_raw_value('FFFFFFFFFFFFFFFF',dt); dbms_output.put_line(to_char(dt)); end
FRI FIFTEEN THOUSAND SIX HUNDRED FIFTY-FIVE


Please do not ask which month is FRI...

Cheers,
Laurent

25 January 2008 at 08:51:00 GMT-8  
Blogger Laurent Schneider said...

note Julian Day does not break, but TO_CHAR ...

SQL> alter session set nls_date_format='J';

Session altered.

SQL> select trunc(date '-4712-01-01','CC'),round(date '9999-12-31','CC') from dual;
TRUNC(D ROUND(D
------- -------
0032141 5373851


Well, J is not signed, but 4800-01-01 is julian day -32141

25 January 2008 at 08:57:00 GMT-8  

Post a Comment

<< Home