Re: Millennial Fun with Oracle Dates

In article <84t1pr$f7f$1_at_kermit.esat.net>,   "Keith Jamieson" <jamiesonk_at_phoenix.ie> wrote:
>
> >
> >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.

Yeah, you're right, I realized that later, for a leap year 367 days would be correct. If year 0 exists, by either Julian or Gregorian calendar rules it would have been a leap year.

>

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

Cool...two different answers. What version of Oracle are you using? The results I posted here are from Oracle 8i, tested on both NT and Solaris.

>
> >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
Again, must be a difference in the versions, because the results are cut-n-pasted straight from my sqlplus screen. Sounds like you're implying I made this stuff up...

<snip>
> I think that maybe a little too much alcohol may have been consumed
when you
> were trying to work this out.
Don't embarrass yourself in public by making unfounded accusations. What you see in the post are the actual SQL statements and results - granted I was wrong about the 367 days, but the rest of the stuff is just as Oracle 8i computed it (perhaps our computers had too much alcohol?)

I refrained from making any implication that there's a bug in Oracle's date processing logic...though based on the results one might be tempted to make such an accusation. Especially in the light of you getting different results from the same SQL statements, at least one version must be wrong.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jan 05 2000 - 23:25:20 CET