Re: Converting serial number to Oracle date datatype

Hi Stéphane,

Your advice works great. Thanks.

Henrik

In article <370EB6CA.67C0_at_oriolecorp.com>,   sfaroult_at_oriolecorp.com wrote:
> henrik_m_at_my-dejanews.com wrote:
> >
> > Hello,
> > I want to change a serial number to an Oracle date datatype, e.g. the
> > serial number 35079.5 to '15-JAN-1996 12:00'. How is this possible?
> > I am using MS Excel to display data from a table in an Oracle database. The
> > primary key of the table is a date column which uses timestamps as well as
> > datestamps. Therefore, to locate the exact row in the database corresponding
> > to the row in Excel, I have to convert a serial number to an Oracle date
> > datatype.
>
> I don't know how exactly Excel serial numbers are computed (and I am too
> lazy to reverse-compute your example) bu the conversion should be
> extremely easy. When you substract two dates, the difference is a number
> (not an integer) - and when you add or sustract a number to or from a
> date, it is assumed to be an offset in days and the result is a date.
> For instance <any date> + 0.5 will give you a date 12 hours later than
> the first one. So all you have to do is to know which date corresponds
> to a 0 Excel serial number (I would be tempted to say January 1st 1970
> 00:00:00 but from MicroSoft you can expect anything) and add your serial
> number to it to get the correct Oracle date which you just have to
> format to your taste.
> --
> Regards,
>
> Stéphane Faroult
> Oriole Corporation
> ------------------------------------------------------------------
> http://www.oriolecorp.com, designed by Oracle DBAs for Oracle DBAs
> ------------------------------------------------------------------
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Apr 15 1999 - 11:29:01 CDT