SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

convert varchar2 with T in it to date

user5716448Feb 19 2020 — edited Feb 19 2020

Hi,

version 11.2.0.3

Have following varchar2 column which get from separate system and want to store it as date in our system.

2020-01-30T12:56:41.0000000

Using below

TO_DATE (substr(regexp_replace(EVENT_TIMESTAMP,'T|.0000000',''),1,18) , 'YYYY-MM-DDHH24:MI:SS') EVENT_TIMESTAMP

works

However not sure why getting message date format picture ends before converting entire input string ORA-01830 as length 18.

TO_DATE (regexp_replace(EVENT_TIMESTAMP,'T|.0000000','') , 'YYYY-MM-DDHH24:MI:SS') EVENT_TIMESTAMP1 -- gives ORA-01830 error

Also is there a tidier/less verbose way to translate this string to a date?

We have no control of data being received.

Thanks

This post has been answered by BEDE on Feb 19 2020

Jump to Answer

Added on Feb 19 2020

11 comments

327 views