构造6个月内日期过滤WHERE子句时遇ORA-01861错误求解

错误原因 #

你的代码触发ORA-01861错误的核心是日期隐式转换导致格式不匹配，具体问题如下：

• 右侧表达式中，LAST_DAY(ADD_MONTHS(SYSDATE, -6))本身是DATE类型，直接用SUBSTR截取时，Oracle会自动将DATE转为字符串，转换格式依赖当前会话的NLS_DATE_FORMAT参数（比如可能是DD-MON-RR而非你预期的YYYY-MM-DD）。此时SUBSTR(...,1,10)截取的字符串格式与TO_DATE指定的YYYY-MM-DD不匹配，就会抛出格式错误。
• 额外问题：将DATE类型字段CONTACT_DATE转为字符串比较，会导致字段索引失效（若存在索引），降低查询效率，还增加了格式不一致的风险。

正确写法 #

针对你的需求（筛选CONTACT_DATE截断为日期值后，属于当前日期6个月内的数据），推荐直接操作DATE类型，避免字符串转换：

场景1：筛选日期 ≥ 6个月前的当月最后一天 #

如果逻辑是保留从6个月前当月最后一天到现在的数据：

WHERE TRUNC(P.CONTACT_DATE) >= LAST_DAY(ADD_MONTHS(SYSDATE, -6))

场景2：筛选日期 ≥ 当前日期往前推6个月的当天 #

如果逻辑是保留从当前日期减去6个月的当天到现在的数据（比如今天是2024-05-20，就保留2023-11-20及之后的数据）：

WHERE TRUNC(P.CONTACT_DATE) >= ADD_MONTHS(TRUNC(SYSDATE), -6)

关键说明 #

• TRUNC(P.CONTACT_DATE)：将DATE字段截断为纯日期（去掉时分秒部分），满足你“仅日期值（YYYY-MM-DD）”的需求。
• 所有日期计算直接操作DATE类型，避免隐式转换，既保证逻辑可靠，又能利用字段索引提升查询性能。

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