Hi,
I have data in the format 2019-02-21T13:58:18.000+00:00 in a varchar2 column(loaded into staging table from a CSV file). When merging this staging table I need to put this value into a DATE data type column. How to do this ? Format of the date need to be same.
Currently when I do without any conversion I am getting below error
declare
*
ERROR at line 1:
ORA-01861: literal does not match format string
ORA-06512: at line 24
Any help or guidance is appreciated
Thanks,
Girish
Your date value has fractional seconds and timezone details. Oracle Database DATEs only go down to the second. And don't support timezones.
So either:
You need to trim this information off the end
with val as (
select '2019-02-21T13:58:18.000+00:00' v
from dual
)
select to_date (
substr ( v, 1, 19 ) ,
'FMYYYY-MM-DD"T"HH24:MI:SS'
) dt
from val;
DT
21-FEB-2019 13:58:18 OR
You can use the # format to ignore the characters at the end:
with val as (
select '2019-02-21T13:58:18.000+00:00' v
from dual
)
select to_date ( v, 'FMYYYY-MM-DD"T"HH24:MI:SS.###+##:##' ) dt
from val;
DT
21-FEB-2019 13:58:18