Oracle VARCHAR日期字段过滤报错ORA-01843求助

我一眼就看出问题出在哪了——你在拿字符串类型的日期和转换后的日期类型直接比较，Oracle会偷偷做隐式转换，这就是报错的根源！

具体来说：你写的SQL里，左边的datecreate是VARCHAR类型，右边是to_date()生成的日期。Oracle执行时会把左边的字符串隐式转成日期，但它用的是当前会话的NLS_DATE_FORMAT设置，不是你指定的dd/mm/yyyy hh24:mi:ss。比如如果你的会话格式是mm/dd/yyyy，那24/12/2016这种字符串转日期时，会把24当成月份，自然就报“无效月份”了。

给你两个解决方案，按需选择：

临时快速解决（不改表结构） #

直接把左边的datecreate也用显式的to_date()转换，指定和你数据匹配的格式，让两边都是日期类型再比较，就不会有隐式转换的坑了：

select * 
from table 
where to_date(datecreate, 'dd/mm/yyyy hh24:mi:ss') >= to_date('01/02/2018 00:00:00', 'dd/mm/yyyy hh24:mi:ss') 
  and to_date(datecreate, 'dd/mm/yyyy hh24:mi:ss') <= to_date('28/02/2018 23:59:00', 'dd/mm/yyyy hh24:mi:ss');

⚠️ 注意：这种写法会让datecreate上的索引失效，但你只有1000条数据，完全不用担心性能问题。如果以后数据量变大，建议用下面的彻底方案。

彻底解决（推荐，从根源避免问题） #

把datecreate字段的类型从VARCHAR改成DATE（或TIMESTAMP）——日期数据就该存日期类型，别用字符串！步骤如下：

1. 先加一个临时DATE字段：

   alter table table add temp_datecreate date;
   

2. 把原字段的字符串日期转成日期类型存到临时字段：

   update table set temp_datecreate = to_date(datecreate, 'dd/mm/yyyy hh24:mi:ss');
   

3. 验证临时字段数据没问题后，删掉原VARCHAR字段：

   alter table table drop column datecreate;
   

4. 把临时字段重命名回原来的名字：

   alter table table rename column temp_datecreate to datecreate;
   

改完之后，你原来的SQL就能直接用了，而且还能用到字段上的索引，性能更好！

额外小提示：检查脏数据 #

如果转换时还是报错，可能是datecreate里有不符合dd/mm/yyyy hh24:mi:ss格式的脏数据，可以用这个SQL找出问题记录：

select id, datecreate
from table
where validate_conversion(datecreate as date format 'dd/mm/yyyy hh24:mi:ss') = 0;

先把这些脏数据处理掉，再执行转换就没问题了。

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