Re: Internal date format (numerical)

"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? Received on Thu Dec 01 2005 - 16:55:55 CST