为什么Oracle ADD_MONTHS传入SYSDATE与硬编码日期返回结果不同

问题原因 #

你遇到的问题本质是Oracle日期类型的时间精度差异导致的：

• SYSDATE 是包含当前时分秒信息的日期类型，你单独执行select add_months(sysdate, -6) from dual时看到的03/02/2021只是客户端默认只展示了日期部分，实际返回值还带有执行查询时刻的时分秒，比如你在9月2日下午2点执行查询，实际阈值是2021-03-02 14:00:00
• 你表中CLOSED_DT为03/02/2021的记录，存储的时间是当天的00:00:00，显然小于带时分秒的阈值，因此不会被检索到
• 之前硬编码用TO_DATE('09/02/2021', 'mm/dd/yyyy')生成的日期默认不带时分秒（即当天0点），因此add_months后得到的阈值是2021-03-02 00:00:00，可以匹配到3月2日当天的所有记录

解决方法 #

只需要用TRUNC()函数截断SYSDATE的时分秒部分，让它和硬编码的日期逻辑一致即可，修改后的查询语句如下：

select case_no, closed_dt from table1
where closed_dt >= add_months(TRUNC(sysdate), -6)
order by closed_dt

TRUNC(sysdate)会返回当前日期的0点，经过add_months计算后得到的6个月前的阈值也是对应日期的0点，就能完整检索到6个月前当日到当前的所有记录了。

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