[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 962
  • Last Modified:

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" ?
0
Alaska Cowboy
Asked:
Alaska Cowboy
  • 3
  • 3
2 Solutions
 
slightwv (䄆 Netminder) 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Aaron ShiloCommented:
use the TO_TIMESTAMP conversion function
0
 
slightwv (䄆 Netminder) 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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now