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-

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

So I am testing with this data: 2011-03-31-, 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),'') returns 31-Mar-2011 2:38:07 PM

but how do I return "31-Mar-2011 2:38:07.000000 PM" ?
Alaska CowboyAsked:
Who is Participating?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
with FF:

select to_timestamp('2011-03-31-','YYYY-MM-DD-HH24:MI:SS.FF') from dual
slightwv (䄆 Netminder) Commented:
forgot the second part:

select to_char(
      'DD-Mon-YYYY HH:MI:SS.FF AM'
from dual
Alaska CowboyAuthor Commented:

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 ?
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
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>what does "FF" stand for ?

Fractional seconds:
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
Alaska CowboyAuthor Commented:
all good ! thanks.
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.