Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Internal date format (numerical)

Re: Internal date format (numerical)

From: Jim Kennedy <kennedy-downwithspammersfamily_at_attbi.net>
Date: Fri, 2 Dec 2005 07:10:04 -0800
Message-ID: <n96dnS_eYKv4_g3enZ2dnUVZ_vmdnZ2d@comcast.com>

"David Cressey" <david.cressey_at_earthlink.net> wrote in message news:00Xjf.8121$N45.7545_at_newsread1.news.atl.earthlink.net...
>
> "Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message
> news:s76dnW9Dmpm2GhLeRVn-jA_at_comcast.com...
> >
> > "David Cressey" <david.cressey_at_earthlink.net> wrote in message
> > news:%bLjf.8477$wf.4100_at_newsread3.news.atl.earthlink.net...
> > >
> > > "Guy" <guhar1_at_yahoo.com> wrote in message
> > > news:1133369648.634627.260420_at_g14g2000cwa.googlegroups.com...
> > >> Why to I need to add the number 2 to the following SQL to get the
real
> > >> numerical equivalent of a date?
> > >>
> > >> select 2 + (to_date('01-12-2005', 'DD-MM-YYYY') -
> > >> to_date('01-01-1900','DD-MM-YYYY')) from dual
> > >>
> > >> I use a Delphi function EncodeDate(2005, 12, 1) which returns 38687.
> > >> If I dont add 2 to the sql, I dont get the proper numerical value.
> > >>
> > >> The same function works fine with Sql*Server.
> > >>
> > >
> > > Ah, yes!!! It's the wonderful vagaries of the calendar!
> > >
> > > First off, check out what you get from Delphi for EncodeDate(1900, 1,
> 1).
> > > Unless I miss my guess, the answer will be 2. If I'm wrong,
disregard
> > > the
> > > rest of this post.
> > >
> > > The reason you are off by two days from what you expect is due to two
> one
> > > day errors.
> > >
> > > The first error is a fencepost error. In order get the number of days
> > > since
> > > the base date, you have to subtract the day whose number is ZERO not
> the
> > > day whose number is ONE. If January 1, 1900 were day 1, then the day
> > > zero
> > > is actually December 31, 1899.
> > >
> > > That explains one days discrepancy. But what about the other day?
> > >
> > > The reason for this other day has to do with the infamous "year 1900
> leap
> > > year bug". You see, both Lotus 1-2-3 and MS Excel were coded as if
the
> > > year
> > > 1900 had been a leap year. But, if you take a close look at the
> Gregorian
> > > calendar you'll find that it was not! So the only way Oracle could
make
> > > it's day numbering agree with the two spreadsheets, and not include
the
> > > year
> > > 1900 leap year bug, was to start one day early!
> > >
> > > So day zero in Oracle is December 30, 1899 and not December 31 as you
> > > might
> > > have expected. For days between Jan 1, 1900 and Feb 28, 1900, Oracle
> and
> > > MS Excel are off by a day. After March 1, 1900, they agree!
> > >
> > > How's this... clear as mud?
> > >
> > >
> >
> > David,
> >
> > Look at the other posts on this -- 'day 0' in Oracle is way back in the
> 5th
> > millenium BCE
> >
> > ++ mcs
> >
> >
>
> Actually, I believe that Oracle stores about half of its range as "days
> before day zero", which I'll take as "negative dates".
> But no matter, your point is valid.
>
> My post should have dealt the the question of "what is "Day zero in
Delphi"?
>
> I wonder what you get if you do, EncodeDate (1899, 12, 30) in Delphi. ?
>
>
>

No if you look in the Oracle doco it stores day 0 back in 5th millenium BC. Jim
>
Received on Fri Dec 02 2005 - 09:10:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US