I am getting error in date format in oracle

Question

I want the date format in yyyy-mm-dd but I am getting error

Here is the code

table creation

create table orders
  2  (ord_no int,
  3   purch_amt float,
  4  ord_date date,
  5  customer_id int,
  6  salesman_id int);

insertion in the table

insert all
  2  into orders(ord_no,purch_amt,ord_date,customer_id,salesman_id)values(70001,150.5,'2012-10-05',3005,5002)
  3  into orders(ord_no,purch_amt,ord_date,customer_id,salesman_id)values(70009,270.65,'2012-09-10',3001,5005)
  4  into orders(ord_no,purch_amt,ord_date,customer_id,salesman_id)values(70002,65.26,'2012-10-05',3002,5001)
  5  into orders(ord_no,purch_amt,ord_date,customer_id,salesman_id)values(70004,110.5,'2012-08-17',3009,5003)
  6  into orders(ord_no,purch_amt,ord_date,customer_id,salesman_id)values(70007,948.5,'2012-09-10',3005,5002)
  7  into orders(ord_no,purch_amt,ord_date,customer_id,salesman_id)values(70005,2400.6,'2012-07-27',3007,5001)
  8  into orders(ord_no,purch_amt,ord_date,customer_id,salesman_id)values(70008,5760,'2012-09-10',3002,5001)
  9  select * from dual;
into orders(ord_no,purch_amt,ord_date,customer_id,salesman_id)values(70001,150.5,'2012-10-05',3005,5002)

I am getting this error

into orders(ord_no,purch_amt,ord_date,customer_id,salesman_id)values(70001,150.5,'2012-10-05',3005,5002)
                                                                                 *
ERROR at line 2:
ORA-01861: literal does not match format string

Answer 1

'2012-10-05' is a string, not a DATE. Either

• use DATE literal: DATE '2012-10-05'
• explicit format specifier TO_DATE('2012-10-05', 'YYYY-MM-DD')
• set default date format alter session NLS_DATE_FORMAT = 'YYYY-MM-DD';

Answer 2

Unless that is the default date format for the database or the default for the session (set via NLS_DATE_FORMAT, it won't work. I prefer to use the TO_DATE() function and specify the format and not rely on the default.