| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Converting serial number to Oracle date datatype
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
 |
 |