How to convert string format to date?

advertisements

Hi any one please help me...

The below mentioned line is one column data.

column1:Supplier Setup request submitted on 2016-01-06 06:00:25.141 by WFS ADMINISTRATOR

I want to divide into two columns like columnA and columnB:

columnA                   columnB
WFS ADMINISTRATOR        2016-01-06 06:00:25.141

I wrote query by using sub string and instring functions:

select SUBSTR(column1, INSTR(column1,'on',1)+2, INSTR(column1,'by',1)-INSTR(column1,'on',2)-2) as columnB
from xyz

It is working fine, but I want to display date format like MM/DD/YYYY HH24:MI:SS.

I can't able to get date format...


You get the datetime part with

regexp_replace(col, '.*on (.*) by.*', '\1')

You convert such string to timestamp with

to_timestamp(str, 'yyyy-mm-dd hh24:mi:ss.ff')

And you convert a timestamp to a string formatted MM/DD/YYYY HH24:MI:SS with

to_char(ts, 'mm/dd/yyyy hh24:mi:ss')

Together:

to_char
(
  to_timestamp
  (
    regexp_replace(col, '.*on (.*) by.*', '\1'), 'yyyy-mm-dd hh24:mi:ss.ff'
  ), 'mm/dd/yyyy hh24:mi:ss'
)