Re: Internal date format (numerical)

"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. ? Received on Fri Dec 02 2005 - 06:22:20 CST