How to convert string to date format?

Question

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...

Tag dbms used! (Too many products are far from ANSI SQL compliant when it comes to date/time.) — jarlh, Jan 12 '16 at 11:50

score 0 · Answer 1 · answered Jan 12 '16 at 11:54

0

assuming that columnB now hold the date part:

to_date(columnB,'MM/DD/YYYY HH24:MI:SS');

answered Jan 12 '16 at 11:54

HardLeeWorking

Thanks, I have used to_date function but getting this error **oracle.xdo.XDOException: java.sql.SQLDataException: ORA-01843: not a valid month** query is select to_date(SUBSTR(column1,INSTR(column1,'on',1)+2,INSTR(column1,'by',1)-INSTR(column1,'on',2)-2),'MM/DD/YYYY HH24:MI:SS') as columnB from xyz – bharathi Jan 12 '16 at 12:12
is columnB data type set as 'DATE'? – HardLeeWorking Jan 12 '16 at 12:20
Try changing it to to_date(columnB, 'yyyy-mm-dd hh24:mi:ss') – HardLeeWorking Jan 12 '16 at 12:27
please help me how to convert – bharathi Jan 12 '16 at 12:36
i changed but still getting error as **oracle.xdo.XDOException: java.sql.SQLDataException: ORA-01830: date format picture ends before converting entire input string** select to_date(SUBSTR(column1,INSTR(column1,'on',1)+2,INSTR(column1,'by',1)-INSTR(colum‌n1,'on',2)-2),'yyyy-mm-dd HH24:MI:SS') as columnB from xyz – bharathi Jan 12 '16 at 12:55

Thorsten Kettner · Accepted Answer · 2016-01-12T12:44:47.247

0

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'
)

edited Jan 12 '16 at 12:44

answered Jan 12 '16 at 12:22

Thorsten Kettner

Hi,the above query is working.But that column contains as below shown converted_on --------------------- 2016-01-06 06:00:25.141 2015-12-28 19:50:23.98 2015-10-19 00:09:37.314 04/22/2013 16:43:08 by using your query it converted 'yyyy-mm-dd hh24:mi:ss.ff' to 'mm/dd/yyyy hh24:mi:ss'. but not converting for 'mm-yyyy-hh24:mi:ss.ff' to 'mm/dd/yyyy hh24:mi:ss'. it throws error like as **oracle.xdo.XDOException: java.sql.SQLDataException: ORA-01843: not a valid month** – bharathi Jan 13 '16 at 06:35
Are you saying that your date strings differ? So while one string contains '2016-01-06 06:00:25.141', another contains '04/22/2013 16:43:08'? Maybe even more formats? You can deal with that with CASE expressions (e.g. if a dash in in position 5 it is the ANSI format, if a slash is in position 3 it is MM/DD... etc.). You can do this in two steps: Separate the string in a derived table (subquery), then select from this to to the evaluation. – Thorsten Kettner Jan 13 '16 at 07:13
But maybe you'd prefer to write a PL/SQL function for this. SQL is not really made for things like these, and a programming language like PL/SQL may be more convenient. You could even use try and fail methods (trying to convert with one format, catch the exception, try another format, ...). – Thorsten Kettner Jan 13 '16 at 07:14

2 Answers2