| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Has Oracle actually erased the birth of Christ from the calendar?
Hi David. It seems that I could do with some education on the history of
calendars. I would still be a little suspicious of the statement that
Greenwich mean time is a base for Julian dates!
Anyhow, what I am concerned about is errors in date computations in Oracle. I believe Norman Dunbar shares my concern [based on his reply to my posting]. For example, using SQL*Plus [against 8.1.6]:
SQL> l
1 select to_date('0001bc/12/31','yyyybc/mm/dd') -
to_date('0001ad/01/01','yyyybc/mm/dd')
2* as DaysDiff from dual
SQL> /
DAYSDIFF
-367
We see here that subtracting 31-Dec-0001bc from 01-Jan-0001ad does not produce a result of 1 [as expected] but 367.
In my understanding, subtracting two dates should give the interval between the two. How the Oracle server performs the calculation internally (i.e. whether it uses the correct Julian base or otherwise) is not an issue.The correct result is!
Futher more, try this:
SQL> select to_char(to_date('0001bc/12/31','yyyybc/mm/dd'),'Day') as Day from dual;
DAY
SQL> c ,0001bc/12/31,0001ad/01/01,
1* select to_char(to_date('0001ad/01/01','yyyybc/mm/dd'),'Day') as Day
from dual
SQL> /
DAY
It comes as a surprise to me that the day after Wednesday is Saturday! Maybe Pope Gregory was more active than I thought! I will try to find the time to educate myself on the Julian dates. Mean time, I think Oracle needs to look at their Date computations.
Cheers from [wet] Brisbane!
"David Fitzjarrell" <oratune_at_msn.com> wrote in message
news:32d39fb1.0111091202.21187e34_at_posting.google.com...
> 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 - 19:47:35 CST
 |
 |