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.
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?
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 😉
>> 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
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
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.
(full) year must be between -4713 and +9999,
this error message is ridiculous, it cannot be -4713 but it can be 9999
Also note that -4712 is not a leap year 😮
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 😎
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
Interesting experiment, but it may detract from Oracle sales to archaelogical firms. Some fundamentalist Christian organizations may be encouraged to buy Oracle, however. 🙂
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.
“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.
🙂
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) …
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 😉
Do you want the export?
ok, you can mail it to me
Did you receive the export file?
yes, I will have a look. thank you