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" ?
LVL 1
Alaska CowboyAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
with FF:

select to_timestamp('2011-03-31-14.38.07.004400','YYYY-MM-DD-HH24:MI:SS.FF') from dual
/
0
 
slightwv (䄆 Netminder) Commented:
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
/
0
 
Alaska CowboyAuthor Commented:
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 ?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Aaron ShiloChief Database ArchitectCommented:
use the TO_TIMESTAMP conversion function
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>what does "FF" stand for ?

Fractional seconds:
http://psoug.org/reference/date_func.html
0
 
Alaska CowboyAuthor Commented:
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
0
 
Alaska CowboyAuthor Commented:
all good ! thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.