Inserting Date gives error ORA-01861: literal does not match format string

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

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