Oracle日期格式报错求助：期望yyyy-mm-dd格式却触发ORA-01861错误

Why This Happens #

That ORA-01861: literal does not match format string error pops up because the date string format you’re using ('2012-10-05') doesn’t match Oracle’s default date format for your session. By default, Oracle typically uses something like DD-MON-RR (e.g., 05-OCT-12), so when you pass a yyyy-mm-dd string, it can’t parse it correctly.

Solutions #

Here are a few reliable ways to fix this, ordered by how robust they are:

1. Explicitly Convert Dates with TO_DATE() #

This is the most foolproof method—no matter what the session’s date format is, this ensures your date is parsed correctly. Wrap each date string in TO_DATE() and specify the format mask:

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

2. Change Your Session’s Date Format #

If you don’t want to tweak every insert line, set the session’s date format to yyyy-mm-dd first. This tells Oracle to expect dates in that format for your current connection:

ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd';

Then run your original insert statement. Just note that this setting only lasts for your current session—once you log out, it’ll revert to the default.

3. Use ANSI Date Literals (Oracle 9i+) #

Another clean option (no function needed) is to use ANSI-standard date literals, which Oracle recognizes automatically regardless of session settings. The syntax is DATE 'yyyy-mm-dd':

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

Pro Tip #

Stick with explicit conversion (methods 1 or 3) whenever possible. It makes your code more portable—you won’t run into issues if you move it to an environment with a different default date format. Avoid changing the database-wide date format unless you absolutely have to, since it can break other code that relies on the original format.

内容的提问来源于stack exchange，提问作者soma