Re: Millennial Fun with Oracle Dates
Date: Thu, 06 Jan 2000 12:00:02 -0800
Message-ID: <3874F442.365261A9_at_vnl.nl>
saustin_deja_at_my-deja.com wrote:
> 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.
As date arithmatic in Oracle is based on Julian calendar, let's take a
closer look:
select to_char(to_date('01-01-0001 AD','MM-DD-YYYY AD'), 'J') from dual;
TO_CHAR
1721424
This is the Julian count of the 1st day of the 1st month of the 1st year of our calendar.
Now for the last day before that:
select to_char(to_date('12-31-0001 BC','MM-DD-YYYY BC'), 'J') from dual;
TO_CHAR
1721057
Hmmm, quite a gap, no? Let's see what the next day is: select to_char(to_date('1721058','J'),'DD-MM-YYYY BC') from dual; TO_CHAR(TO_DA
ORA-01841: (full) year must be between -4713 and +9999, and not be 0 So this must be the 1st day of the illustrious year 0 then.
Going backward from 01-01-0001 AD:
select to_char(to_date('1721423','J'),'DD-MM-YYYY BC') from dual;
TO_CHAR(TO_DA
ORA-01841: (full) year must be between -4713 and +9999, and not be 0 So, this should be Dec, 31st, 0.
Do the arithmatic: 1721423 - 1721058 = 365, add 1 for the boundary, and 0 was a leap year, having
366 days. That's why you need to add 367 days before ending up in a date, out christian calendar
understands.
So, our calendar does not have a year 0 defined, but julian dates work fine
with it. AD/BC seems
off, tho, by 1 year. Wonder what Oracle has to say about that.
Less seriously, BC meaning Before Christ (was born) - right? According to
several well educated
people, Christ was born in 4 BC - go figure.
-- Met vriendelijke groet/kind regards, Frank van Bortel Technical consultant OracleReceived on Thu Jan 06 2000 - 21:00:02 CET