Oracle中DATE类型列设为YYYY-MM-DD格式报错ORA-01861求助

咱们来一步步拆解你的问题，先搞清楚错误根源在哪～

错误原因分析 #

你遇到的ORA-01861: literal does not match the format string错误，主要有两个核心问题：

1. 隐式转换的依赖问题：当你把字符串'2019-09-28'插入DATE类型列时，Oracle会自动做隐式类型转换，但这个转换规则完全依赖当前会话的NLS_DATE_FORMAT设置。如果你的会话默认格式不是YYYY-MM-DD（比如Oracle常见的默认格式是DD-MON-RR），就会出现格式不匹配的报错。
2. 无效且危险的CHECK约束：你添加的CHECK (my_date = TO_DATE(my_date,'YYYY-MM-DD'))完全是多余的，甚至会帮倒忙。因为my_date本身就是DATE类型，Oracle执行TO_DATE(my_date,'YYYY-MM-DD')时，会先把DATE值转换成字符串（用当前会话的NLS_DATE_FORMAT），再尝试转回DATE。如果会话格式不是YYYY-MM-DD，这一步转换直接就会失败，约束本身就会触发错误。

解决方案 #

第一步：移除无效的CHECK约束

DATE类型是Oracle内部的二进制存储，不存在“存储格式”的概念——格式只是你查询时的显示方式。所以根本不需要用CHECK约束来强制所谓的“存储格式”。修改后的建表语句如下：

CREATE TABLE my_table(
    mytable_id VARCHAR(2) PRIMARY KEY,
    my_date DATE NOT NULL
);

第二步：用可靠的方式插入日期

推荐以下两种完全不受环境设置影响的插入方式：

• 方式1：用TO_DATE显式转换（最稳妥）
  直接指定字符串的格式，彻底摆脱对会话NLS设置的依赖：

  INSERT INTO my_table VALUES('01', TO_DATE('2019-09-28', 'YYYY-MM-DD'));
  

• 方式2：使用ANSI日期字面量
  Oracle支持标准的日期字面量写法，格式固定为YYYY-MM-DD，写法简洁且可靠：

  INSERT INTO my_table VALUES('01', DATE '2019-09-28');
  

如果你非要用字符串直接插入（不推荐，因为依赖环境），可以临时修改会话的日期格式：

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
INSERT INTO my_table VALUES('01','2019-09-28');

验证结果 #

执行完上面的插入语句后，再执行查询：

SELECT * FROM my_table;

就能正常看到你期望的结果啦～

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