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

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

How to convert and store a varchar2 value having fractional seconds into date datatype

Hi,

I have a question ->

In a varchar2(50) column in oracle in a table, I have  a value like this -->

LAST_UPDATED_ON
--------------------------------

2012-08-15T14:02:45.567

I need to insert this value as a "date" datatype in another table.

So here in TabB, the column (LAST_UPDATED_ON) is varchar2(50) but in TabA the column is date.

I am trying to do this -->

insert into TabA (LAST_UPDATED_ON)
select to_date(translate(LAST_UPDATED_ON,'T', ' '), 'YYYY-MM-DDHH24:MI:SS')
from TabB;

This gives me error --> "date format picture ends before converting input string"

How can I do this?
0
sunny82
Asked:
sunny82
3 Solutions
 
slightwv (䄆 Netminder) Commented:
Can you not use TO_TIMESTAMP?

select TO_TIMESTAMP('2012-08-15T14:02:45.567', 'YYYY-MM-DD"T"HH24:MI:SS.FF3')  from dual;

If not, try this:

select to_date(replace(regexp_replace('2012-08-15T14:02:45.567','\.[0-9]{3}$'),'T'),'YYYY-MM-DDHH24:MI:SS') from dual;
0
 
lwadwellCommented:
or use substr()
    to_date(substr(translate(last_updated_on,'T', ' '),1,19), 'YYYY-MM-DD HH24:MI:SS')
or
    to_date(substr(last_updated_on,1,19), 'YYYY-MM-DD"T"HH24:MI:SS')
0
 
slightwv (䄆 Netminder) Commented:
>>or use substr()

Ah yes, the KISS method.  Guess I way over-thought that one!
0
 
awking00Commented:
A couple of tweaks to lwadwell's solution. Using '' (i.e. empty string) with the translate function will cause the result to be null so you need to add a character that translates to itself.
SQL> select nvl(translate('abc','a',''),'null') from dual;

NVL(
----
null
SQL> select nvl(translate('abc','xa','x'),'null') from dual;

NV
--
bc
Also, once the 'T' gets translated to null, the substring only needs to be 18 characters, so the resultant query would be like -
SQL> select to_date(substr(translate('2012-08-15T14:02:45.567','xT','x'),1,18),'YYYY-MM-DDHH24:MI:SS') as somedate from dual;

SOMEDATE
---------
15-AUG-12
0
 
sunny82Author Commented:
Thanks all of them worked great.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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