| 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
Eike J. Heinze wrote:
>
> Hi;
> another option is to convert any textual representation back into the Oracle date
> format: TO_DATE( 'text_date', 'format_mask').
> Example: TO_DATE( '12-apr-1999 14:35:23', 'dd-mon-yyyy hh24:mi:ss').
> You may want to read the doc on TO_DATE and it's format options.
> I have only little Excel experience but found it's way of handling date/time IMHO
> _extremely_ annoying, at least compared to Oracle.
> Regards Eike
>
> 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.
> >
> > The closest thing I have found to solving the problem is using the Julian day
> > format as described in "Oracle7 Server SQL Reference Manual", "Operators,
> > Functions, Expressions, Conditions". However, the conversion only allows
> > integers so it is impossible to include timestamps in the conversion.
> >
> > Any suggestions?
> >
> > Henrik
> >
> > -----------== Posted via Deja News, The Discussion Network ==----------
> > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Isn't the excel date just the number of days since some starting point ?
If so, just use normal arithmetic.
If the start point is (say) Jan 1, 1900
then
oracle_date := serial# + to_date('01011900','ddmmyyyy');
--
 |
 |