Millennial Fun with Oracle Dates
Date: Thu, 30 Dec 1999 19:23:14 GMT
Message-ID: <84gaq1$d9$1_at_nnrp1.deja.com>
Hello Friends,
As you may know there are those of us who believe that 1 Jan 2001 is the start of the new millenium, not 1 Jan 2000, because in the Western calendar there's no year zero. So we thought we'd find out what Oracle's take on it is, since they have a calendar that goes back past 4000 B.C.
Who would have guessed how much fun this would be:
First let's see how many days there are between 31 Dec 1 B.C. and 1 Jan 1 A.D. If you believe in no year zero, this should be one day.
select to_date('01-01-0001 AD','MM-DD-YYYY AD')-to_date('12-31-0001 BC','MM-DD-YYYY BC') from dual;
TO_DATE('01-01-0001AD','MM-DD-YYYYAD')-TO_DATE('12-31-0001BC','MM-DD- YYYYBC')
367
Uhh, let me see - not only is there a year zero, it was also a leap year (I guess since it divides by both 4 and 400), and by the way it had one extra day as well. Hope it fell on a long weekend!
OK, maybe there is a year zero. Let's see how many days there are between 31 Dec 1 B.C. and 1 Jan in the year 0:
select to_date('01-01-0000 AD','MM-DD-YYYY AD')-to_date('12-31-0001 BC','MM-DD-YYYY BC') from dual;
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Well I guess you *can* have your cake and eat it too. How about this: what happens when I move forward one day from 31 Dec 1 B.C.
select to_char(to_date('12-31-0001 BC','MM-DD-YYYY BC')+1,'MM-DD-YYYY HH24:MI:SS') from dual ;
TO_CHAR(TO_DATE('12
00-00-0000 00:00:00
Now there's a timestamp for ya! Not only is there a year zero, there's also a month zero and a day zero (maybe that's where the 367th day came from...) But tell me, is the year zero B.C. or A.D.?
select to_char(to_date('12-31-0001 BC','MM-DD-YYYY BC')+1,'MM-DD-YYYY BC HH24:MI:SS') from dual ;
TO_CHAR(TO_DATE('12-31
00-00-0000 00 00:00:00
select to_char(to_date('12-31-0001 BC','MM-DD-YYYY BC')+1,'MM-DD-YYYY AD HH24:MI:SS') from dual ;
TO_CHAR(TO_DATE('12-31
00-00-0000 00 00:00:00
Neither, by golly, it's the year zero dammit!!
How many days will it take me to get from 31 Dec 1 B.C. until A.D. time?
select to_char(to_date('12-31-0001 BC','MM-DD-YYYY BC')+365,'MM-DD-YYYY HH24:MI:SS') from dual ;
TO_CHAR(TO_DATE('12
00-00-0000 00:00:00
Not enough...
select to_char(to_date('12-31-0001 BC','MM-DD-YYYY BC')+366,'MM-DD-YYYY HH24:MI:SS') from dual ;
TO_CHAR(TO_DATE('12
00-00-0000 00:00:00
Still not enough!!
select to_char(to_date('12-31-0001 BC','MM-DD-YYYY BC')+367,'MM-DD-YYYY HH24:MI:SS') from dual ;
TO_CHAR(TO_DATE('12
01-01-0001 00:00:00
OK now I'm cool, after a long 367 days I'm finally in A.D. time.
But now, when does the new millenium start? 2 Jan 2000?
Any historians out there using Oracle to compute durations between B.C. and A.D. might want to go check their arithmetic once more...
Cheers
Stuart "much too much time on my hands" Austin
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Dec 30 1999 - 20:23:14 CET