Insert date into WSH_Delivery_Details_Interface (https://docs.oracle.com/cloud/r13_update17c/scmcs_gs/OEDSC/WSH_DELIVERY_DETAILS_tbl.htm) throws this error
Query :
insert into WSH_DEL_DETAILS_Interface
(DELIVERY_DETAIL_INTERFACE_ID, CREATION_DATE, Date_Requested)
values
(30010985553,
TO_DATE('11/12/2018T05:10:30-00:00', 'DD/MM/YYYY '),
TO_DATE('11/12/2018', 'DD/MM/YYYY'));
Sample Record in creation_date and date_requested column:
Date_Requested Creation_Date
16-JUN-10 17-JUN-10 03.40.31.865000000 PM
Advertisement
Answer
The error can be reduced to:
select TO_DATE('11/12/2018T05:10:30-00:00','DD/MM/YYYY ') from dual;
Error report -
ORA-01861: literal does not match format string
Which is reasonable as it clearly doesn’t match. You need to include the time elements in your format mask, and also a character literal for the fixed ‘T’, and for the fixed time zone offset:
select TO_DATE('11/12/2018T05:10:30-00:00','DD/MM/YYYY"T"HH24:MI:SS"-00:00"') from dual;
TO_DATE('11/12/2018
-------------------
2018-12-11 05:10:30
If that ‘time zone’ part isn’t fixed and needs to be honoured, then you can use to_timestamp_tz()
instead of to_date()
:
select TO_TIMESTAMP_TZ('11/12/2018T05:10:30-00:00','DD/MM/YYYY"T"HH24:MI:SS.FFTZH:TZM')
from dual;
TO_TIMESTAMP_TZ('11/12/20
-------------------------
2018-12-11 05:10:30.0 GMT
I’ve included .FF
in the format model as well as the time zone offset elements (you could use TZR
instead of TZH:TZM
if you might be passed regions instead of offsets), since your example of existing data has fractional seconds, even though your literal string does not in this case
And you can cast()
that to a date or plain timestamp if necessary, or possibly normalise to UTC if the input values can be other zones/offsets:
select TO_TIMESTAMP_TZ('11/12/2018T04:10:30-01:00','DD/MM/YYYY"T"HH24:MI:SS.FFTZH:TZM') as orig,
SYS_EXTRACT_UTC(
TO_TIMESTAMP_TZ('11/12/2018T04:10:30-01:00','DD/MM/YYYY"T"HH24:MI:SS.FFTZH:TZM')) as utc
from dual;
ORIG UTC
---------------------------- ---------------------
2018-12-11 04:10:30.0 -01:00 2018-12-11 05:10:30.0