« Why cannot I use subquery there?
milliseconds in alert log »

What is the lowest and highest possible date in Oracle?

using trunc and round I cannot get lower than -4800 nor higher than 10001 :twisted:


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.

This entry was posted on Friday, January 25th, 2008 at 10:57 and is filed under oracle, sql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Post to : del.icio.us - Digg it - Furl - reddit - Spurl

Put your code in <code> and </code> tags

17 Responses to “What is the lowest and highest possible date in Oracle?”

  1. Dominic Brooks Says:
    January 25th, 2008 at 12:39

    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 Says:
    January 25th, 2008 at 12:59

    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 Says:
    January 25th, 2008 at 16:33

    >> 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 Says:
    January 25th, 2008 at 17:52

    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 Says:
    January 25th, 2008 at 18:09

    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 Says:
    January 25th, 2008 at 18:26

    (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 :-o

  7. Laurent Schneider Says:
    January 25th, 2008 at 18:34

    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-)

  8. Laurent Schneider Says:
    January 25th, 2008 at 18:40

    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 Says:
    January 26th, 2008 at 00:31

    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 Says:
    January 26th, 2008 at 03:55

    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 Says:
    January 28th, 2008 at 16:23

    “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 Says:
    February 24th, 2008 at 10:57

    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 Says:
    February 24th, 2008 at 16:38

    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 ;)

  14. Max Says:
    February 25th, 2008 at 08:51

    Do you want the export?

  15. Laurent Schneider Says:
    February 25th, 2008 at 15:48

    ok, you can mail it to me at laurentschneider@yahoo.com

  16. Max Says:
    March 8th, 2008 at 01:24

    Did you receive the export file?

  17. Laurent Schneider Says:
    March 8th, 2008 at 09:08

    yes, I will have a look. thank you

Leave a Reply

Use <code> and </code> to post code