literal does not match format string in 12c Oracle

Question

I am working on oracle 12c but when I execute my query, I'm getting

literal does not match format string

Below is my Query :

INSERT INTO customers
  (customer_id, ciams_uid, title, fname, lname, email,
   dob, store_num, store_name, mobilephone, is_medical_card,
   scheme_number, status_code, create_date_time, last_update_time,
   is_locked, ciams_token, store_phone)
VALUES
  (1, '5', 'test', 'test', 'test', 'test@gmail.com',
   '2014-07-07', '5555555', 'test', '7845125487', 0,
   4555, 'klkl', '2014-07-01', '2014-07-07',
   0, 'dsdssd', '46464646');

I want to insert the values.

Most likely one (or more) of your columns are datetime/timestamps, and you are only passing in a date literal with no time component. — Tim Biegeleisen, Jun 11 '19 at 12:31
You are passing string literals for dates. Precede the strings with the keyword `DATE` to make them date literals. E.g. `DATE '2014-07-07'`. — Thorsten Kettner, Jun 11 '19 at 12:32

score 2 · Accepted Answer · answered Jun 11 '19 at 12:31

2

It seems you need to format your date values

such as date'2014-07-07' or to_date('2014-07-07','yyyy-mm-dd')

answered Jun 11 '19 at 12:31

Barbaros Özhan

59,113
10
31
55

score 1 · Answer 2 · answered Jun 11 '19 at 13:03

You are providing strings in places where DATE datatypes are needed.

Oracle sees this and attempts to coerce your string '2014-07-07' into a DATE datatype. This is done implicitly using your current setting for NLS_DATE_FORMAT.

Oracle docs for NLS_DATE_FORMAT

You should not rely on the setting of NLS_DATE_FORMAT for implicit date conversions. Rather, explicitly include either a DATE literal, a TIMESTAMP literal, or a TO_DATE function call.

Oracle docs for datetime literals

Examples:

SELECT DATE'2014-07-07' FROM DUAL;
SELECT TIMESTAMP'2014-07-07 00:00:00' FROM DUAL;
SELECT TO_DATE('2014-07-07','YYYY-MM-DD') FROM DUAL;

literal does not match format string in 12c Oracle

2 Answers2