Skip to content
Advertisement

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 :

Sample Record in creation_date and date_requested column:

Advertisement

Answer

The error can be reduced to:

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:

If that ‘time zone’ part isn’t fixed and needs to be honoured, then you can use to_timestamp_tz() instead of to_date():

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:

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement