Re: Millennial Fun with Oracle Dates
Date: Thu, 06 Jan 2000 00:39:21 GMT
Message-ID: <850o7n$pmr$1_at_nnrp1.deja.com>
[Quoted] [Quoted] On a (slightly) more serious note, just in case someone out there has
[Quoted] not already been 'bitten' by this one (or figured out what the heck is
going on if they have) -
In Oracle Forms, if you are storing a date to a GLOBAL variable, then
[Quoted] populating a date with the GLOBAL, the results may suprise you now that
[Quoted] we are in 2000. Oracle stores the date to the GLOBAL as a 9 character
[Quoted] string in 'DD-MON-YY' format, when it is reconverted to a DATE, it
assumes the current century - not what you want if the original date
was in the 1900's!
Paul
In article <84t1pr$f7f$1_at_kermit.esat.net>,
"Keith Jamieson" <jamiesonk_at_phoenix.ie> wrote:
>
> saustin_deja_at_my-deja.com wrote in message <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')
> >---------------------------------------------------------------------
---Received on Thu Jan 06 2000 - 01:39:21 CET
> >-----
> >
> > 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!
>
> No it didn't. It only had 366 days. If it take sone day to go from
dec 31
> to jan 1, then you have 366 days in the leap the 1 day. equals 367
days. so
> this is correct.
>
> >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.?
> >
> Funny, I get 01-1-0000 so there is no month 0 or day 0, but there is
a year
> 0
>
> >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!!
> >
> NO , if you check it is AD
> >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.
>
> yes, 367 days. 366 days in year 0 and 1 day to go to year 1.
> however you are already in AD 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
> >
> >
> I think that maybe a little too much alcohol may have been consumed
when you
> were trying to work this out.
>
> >
> >
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Before you buy.
>
>
Sent via Deja.com http://www.deja.com/ Before you buy.