Solved

how to format timestamp data to insert to table

Posted on 2011-03-15
7
959 Views
Last Modified: 2012-05-11
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" ?
0
Comment
Question by:Alaska Cowboy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 125 total points
ID: 35140358
with FF:

select to_timestamp('2011-03-31-14.38.07.004400','YYYY-MM-DD-HH24:MI:SS.FF') from dual
/
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35140373
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
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35140403
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 35140405
use the TO_TIMESTAMP conversion function
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 125 total points
ID: 35140415
>>what does "FF" stand for ?

Fractional seconds:
http://psoug.org/reference/date_func.html
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35140519
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
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35142152
all good ! thanks.
0

Featured Post

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question