What is the lowest and highest possible date in Oracle?

using trunc and round I cannot get lower than -4800 nor higher than 10001 ๐Ÿ˜ˆ

SQL> select trunc(date '-4712-1-1','CC') from dual;
TRUNC(DATE'-4712-1-1','CC')
----------------------------------
Thursday 1st January 4800 B.C.

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

17 thoughts on “What is the lowest and highest possible date in Oracle?

  1. Dominic Brooks

    In terms of storage, the documentation, http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT413, says that it can only STORE “dates in the Julian era, ranging from January 1, 4712 BCE through December 31, 9999 CE (Common Era, or ‘AD’). Unless BCE (‘BC’ in the format mask) is specifically used, CE date entries are the default”.

    Seems like there’s a bit of leeway built in the SQL functions.

    Does this mean that we’ll be able to bump our rates up for the Y10000K bug in 8000 years?

  2. Laurent Schneider Post author

    Well, the date is stored in a 32bits formats, so I can STORE date up to 15402 ๐Ÿ˜‰


    SQL> create table t(d date);
    Table created.

    SQL> declare dt date;
    begin dbms_stats.convert_raw_value('FF02010100000000',dt);
    insert into t values(dt);
    dbms_stats.convert_raw_value('0101010100000000',dt);
    insert into t values (dt); end
    /
    PL/SQL procedure successfully completed.

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

    SQL> select * from t;
    D
    ----------------------------------
    Friday 1st January 15402 A.D.
    Monday 1st January 9999 B.C.

    I guess we round we can hit the bug as early as Jan 1st, 9951, be prepared ๐Ÿ˜‰

  3. APC

    >> we can hit the bug as early as Jan 1st, 9951, be prepared

    Given the parlous state of my pension scheme I will almost certainly still be working then ๐Ÿ™

    Cheers, APC

  4. Brian Tkatch

    Interesting. Obviously, the “normal” case is:

    SQL> SELECT DATE ‘9999-12-31’ FROM Dual;

    DATE’9999
    ———
    31-DEC-99

    SQL> SELECT DATE ‘9999-12-31’ + 1 FROM Dual;
    SELECT DATE ‘9999-12-31’ + 1 FROM Dual
    *
    ERROR at line 1:
    ORA-01841: (full) year must be between -4713 and +9999, and not be 0

    SQL> SELECT DATE ‘-4712-01-01′ FROM Dual;

    DATE’-471
    ———
    01-JAN-12

    SQL> SELECT DATE ‘-4712-01-01’ – 1 FROM Dual;
    SELECT DATE ‘-4712-01-01’ – 1 FROM Dual
    *
    ERROR at line 1:
    ORA-01841: (full) year must be between -4713 and +9999, and not be 0

  5. Don Seiler

    Maybe by 9999 Oracle can release a patch to take advantage of the 1 Tbit processors. Either that or the Terminators will have gotten us all.

  6. Laurent Schneider Post author

    (full) year must be between -4713 and +9999,

    this error message is ridiculous, it cannot be -4713 but it can be 9999 :mrgreen:

    Also note that -4712 is not a leap year ๐Ÿ˜ฎ

  7. Laurent Schneider Post author

    and of course zero sometimes works…

    select date ‘0000-01-01’ + 1 from dual;
    DATE’0000-01-01’+1
    ——————-
    02.01.0000 00:00:00

    APC, try to place 1ยฃ at 2%, so you will be have a nice 529’629’966’432’553’372’578’163’638’713’949’309’343’187’320’185’365’854’741’884’373’938’386ยฃ in 9999 ๐Ÿ˜Ž

  8. Laurent Schneider Post author

    by the way, unless they change regulation and in case I survive the terminators, I will not have to face bug 2038 even!

    $ perl -e "use POSIX; print (ctime (2**31-1))"
    Tue Jan 19 04:14:07 2038
    $ perl -e "use POSIX; print (ctime (2**31))"
    Fri Dec 13 21:45:52 1901

  9. Ontario Emperor

    Interesting experiment, but it may detract from Oracle sales to archaelogical firms. Some fundamentalist Christian organizations may be encouraged to buy Oracle, however. ๐Ÿ™‚

  10. Chen Shapira

    I will still be working when time_t wraps around. It reminded me of a nice story out of “Expert C programming”:

    —-
    The on-line manual pages of the original tunefs,
    like all Berkeley commands, ended with a “Bugs” section. In this case, it read:

    Bugs:
    This program should work on mounted and active file systems, but it
    doesn’t. Because the superblock is not kept in the buffer cache, the
    program will only take effect if it is run on dismounted file systems; if
    run on the root file system, the system must be rebooted.
    You can tune a file system, but you can’t tune a fish.

    Even better, the word-processor source had a comment in it, threatening anyone who removed that last phrase! It said:

    Take this out and a UNIX Demon will dog your steps from now until the
    time_t’s wrap around.

    When Sun, along with the rest of the world, changed to SVr4 UNIX, we lost this gem. The SVr4 manpages don’t have a “Bugs” sectionโ€”they renamed it “Notes” (does that fool anyone?). The “tuna fish” phrase disappeared, and the guilty party is probably being dogged by a UNIX demon to this day. Preferably lpd.

    Later in the chapter there is an exercise to the reader to find out when time_t wraps around.

  11. Brian Tkatch

    “Interesting experiment, but it may detract from Oracle sales to archaelogical firms. Some fundamentalist Christian organizations may be encouraged to buy Oracle, however. :)”

    Heathen! It’s nearly a thousand years too early! Sheesh.

    ๐Ÿ™‚

  12. Max

    Can you imagine any way how to insert a date value into a date column which is dumped as: TYP=12 LEN=7: 0,0,44,132,93,64,203?

    We’ve found some of those values within a few rows of some table columns but using dbms_stats.convert_raw_value( ‘00002C845D40CB’, dt ) didn’t reproduce this (resulting dump: Typ=12 Len=7: 255,100,44,132,93,64,203) …

  13. Laurent Schneider Post author

    Max,
    I have notice this behavior and cannot think of another method yet. Well, if you have some values, you maybe can use exp, can you?

    Weird dates by the way ๐Ÿ˜‰

Comments are closed.