Re: Internal date format (numerical)

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Wed, 30 Nov 2005 12:12:24 -0500
Message-ID: <m-adnb5GSojmQBDenZ2dnUVZ_tKdnZ2d@comcast.com>

"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.
>

'real numerical equivalent' according to who/what?

if i google correctly:

"A TDateTime value is stored as a Double value variable. The integral part represents a date part, time part is held in fractional part. The date part of the TDateTime variable represents the number of days that have passed since 12/30/1899. " --
http://delphi.about.com/library/rtl/blrtlTDateTime.htm

Looks like your base date is two days off. so:

SQL> alter session set nls_date_format = 'dd-mm-yyyy';

Session altered.

SQL> select to_date('01-12-2005') - to_date('30-12-1899') from dual;

TO_DATE('01-12-2005')-TO_DATE('30-12-1899')

---

38687

SQL> select to_date('30-12-1899') + 38687 from dual;

TO_DATE('3

---

01-12-2005

++ mcs Received on Wed Nov 30 2005 - 11:12:24 CST