Re: Has Oracle actually erased the birth of Christ from the calendar?

From Note:69028.1, on Metalink:

In terms of limits, Oracle is capable of handling dates from:

  01-JAN-4712 BC 00:00:00
  Julian Day: 1

through

  31-DEC-9999 AD 23:59:59 AD
  Julian Day: 5373484

The Julian Day number is a count of days elapsed since Greenwich mean noon
on 1 January 4712 B.C. in the Julian proleptic calendar. The Julian Date
is the Julian Day number followed by the fraction of the day elapsed since
the preceding noon.

Note that Oracle considers the starting point of the Julian calendar as January 1, 4712 instead of January 1, 4713. This results in erroneous dates prior to January 1, 0001 as they are roughly 365 days (more or less) older than they should be, thus the reason why Oracle considers the Julian date 1721423 part of year 0. And, if the error message is checked, you'll find that Oracle is, indeed, considering that range of dates part of the non-existent year 0. So Sybrand DID read the post, and made the appropriate comment.

As such, I wouldn't trust any dates past January 1, 0001 from within an Oracle database.

David Fitzjarrell
Oracle Certified DBA

"Chris O'Sullivan" <itoys_at_tpg.com.au> wrote in message news:<3bebe8b9_at_dnews.tpgi.com.au>...
> Who said anything about zero? That was what the Oracle server output! If you
> read the log, you would see that there is a problem with the way the Oracle
> server does date calculations which span the [approximate date of the] year
> of the birth of Christ.
>
> I [we?] would appreciate you read the postings before assuming that we have
> no idea what we are talking about! The dates used were Dec 31, 0001bc and
> Jan 1, 0001ad. Between these two dates, the Julian format element (has a gap
> of 367 days. Again no mention of zero! Also, as indicated in the log of the
> SQL*Plus session, when I added 1 to Dec 31, 0001bc the result was returned
> as Julian 0 [which is not supposed to exist] and a date of "00000/00/00"
> [again which is not supposed to exist]. Finally, when the Julian value
> reaches 1721424 [ a gap of 367 ] the date is returned by Oracle as Jan 1,
> 0001ad [again no mention of zero!].
>
> So it seems to me that the only people who keep mentioning zero are you
> guys! Maybe, I need to post this to Microsoft because at least Bill seems to
> listen!
>
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> news:tunmqibkp8vbaa_at_corp.supernews.com...
> >
> > "Chris O'Sullivan" <itoys_at_tpg.com.au> wrote in message
> > news:3bebc631_at_dnews.tpgi.com.au...
> > > This seems like an interesting bug. It was around in 8.1.5. I've just
> > > checked 8.1.6 and same again. What do all you SQL gurus think?
> > >
> > [snip]
> >
> > > This begs the question:
> > >
> > > Is Larry really God? If pope Gregory struggled to get away with 10 days
> > > guess you would have to be God to get away with 367 days!
> > >
> > >
> >
> > I think you should do some more research on the history of the Calendar,
> and
> > the history of the number 0.
> > The year zero has _never_ existed, as people in the Middle Ages couldn't
> > imagine 0 was a proper number.
> >
> > So : there is still only _one_ God, and it's not Larry, and it's not
> > Billyboy
> >
> > Regards
> >
> >
> > --
> > Sybrand Bakker
> > Senior Oracle DBA
> >
> > to reply remove '-verwijderdit' from my e-mail address
> >
> >
> >
Received on Fri Nov 09 2001 - 14:02:20 CST