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.

Answer 1

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;

Answer 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')