Re: Internal date format (numerical)

"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 Received on Thu Dec 01 2005 - 17:28:43 CST