我在oracle的日期格式中得到错误

Question

我想要日期格式为yyyy-mm-dd，但我得到错误

代码

表创建

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

插入表

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)

我得到这个错误

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'是字符串，不是DATE。

• useDATEliteral:DATE '2012-10-05'
• 显式格式说明符TO_DATE('2012-10-05', 'YYYY-MM-DD')
• 设置默认日期格式alter session NLS_DATE_FORMAT = 'YYYY-MM-DD';

Answer 2

除非这是数据库的默认日期格式或会话的默认日期格式(通过NLS_DATE_FORMAT设置)，否则它将不起作用。我更喜欢使用TO_DATE()函数并指定格式，而不是依赖于默认格式。