Re: Internal date format (numerical)

From: David Cressey <david.cressey_at_earthlink.net>
Date: Sat, 03 Dec 2005 06:08:46 GMT
Message-ID: <ODakf.12489$aA2.7437@newsread2.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.
>

Hi Guy,

My first response was a little confused. In part, it was because I was mistaken about how Oracle stores DATES internally. Normally, there is little reason to be concerned with the internal representation, as long as the datatype works correctly.

If you do your select without adding in the extra two in Oracle, like this,

> select (to_date('01-12-2005', 'DD-MM-YYYY') -
> to_date('01-01-1900','DD-MM-YYYY')) from dual

you get 38685, which is the interval in days between Jan 1, 1900 and Dec 1, 2005.

I asked a colleague who has Delphi to do the same thing in Delphi, and here's what he came up with:

From delphi.about.com:

"The date part of the TDateTime variable represents the number of days that have passed since 12/30/1899."

Subtraction arithmetic between TDateTime values returns the actual time difference in days and fractions of a day between the two.

I get:

2 <-- EncodeDate (1900, 1, 1)
38687 <-- EncodeDate(2005, 12, 1)
38685 <-- EncodeDate(2005, 12, 1) - EncodeDate (1900, 1, 1)

Notice that, in Delphi, to get the interval between two dates, you do a subtraction, just as you do in Oracle.
Also notice that you get the same answer: 38685.

So Oracle and Delphi are consistent. I cross checked by hand to see if they are right, and I get the same answer they do. However, when cross checking by hand, I had to take into account that, in the Gregorian calendar, 1900 was NOT a leap year.

I haven't tried it with SQL Server or MS Access, but I would be surprised if they came up with a different answer for the interval.

If you try this calculation in Lotus 1-2-3 or MS Excel, you get an answer that is off by one. That is because those two products erroneously intercalated Feb 29, 1900, a non existent Gregorian date.

HTH. Received on Sat Dec 03 2005 - 00:08:46 CST