asked on

how to format timestamp data to insert to table

I am getting this data and want to format to insert to a timestamp field: 2011-03-31-14.38.07.004400

The vendor field format is CCYY-MM-DD-HH.MM.SS.MMMMMM

So I am testing with this data: 2011-03-31-14.38.07.004400, which I've loaded to a character field.

how do I format "to_date" to then insert into a timestamp field?

This formatting works:
to_date(substr(ml.clm_adjdn_beg_tmstp,1,19),'yyyy-mm-dd-hh24.mi.ss') returns 31-Mar-2011 2:38:07 PM

but how do I return "31-Mar-2011 2:38:07.000000 PM" ?

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

forgot the second part:

select to_char(
to_timestamp('2011-03-31-14.38.07.004400','YYYY-MM-DD-HH24:MI:SS.FF'),
'DD-Mon-YYYY HH:MI:SS.FF AM'
)
from dual
/

Alaska Cowboy

ASKER

slightwv,

ok the first one worked anyway. I did not know about "to_timestamp", I've only used "to_date".

So I got it to work and will test it out more fully now.

curious - what does "FF" stand for ?

Aaron Shilo

use the TO_TIMESTAMP conversion function

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Alaska Cowboy

ASKER

ashilo - thanks

slightwv - thanks too.

I'll keep this open this afternoon if you don't mind, while I test out the insert via Pl*Sql

Alaska Cowboy

ASKER

all good ! thanks.