Oracle日期计算疑问：为何某SQL语句返回负年龄值？

咱们先拆解核心问题：这本质是Oracle对两位年份日期的解析规则在搞鬼，和你修改NLS_DATE_FORMAT没生效的原因也息息相关。

为什么两个SQL结果一正一负？ #

Oracle对两位年份的解析逻辑，取决于你日期格式模型里的YY或RR（Oracle默认常用RR格式），结合当前年份的后两位来判断世纪：

RR格式的核心规则：如果当前年份的后两位落在00-49区间，那么输入的两位年份：

• 00-49 → 解析为2000-2049年（本世纪）
• 50-99 → 解析为1950-1999年（上世纪）

拿你的SQL举例：

• 执行TO_DATE('30/11/47')时，假设当前是2024年（后两位24，属于00-49），47会被解析成2047-11-30——这个日期比当前日期晚，所以MONTHS_BETWEEN(当前日期, 2047-11-30)得到负数，除以12再取上整就变成了-29。
• 而TO_DATE('30/11/57')里的57属于50-99区间，会被解析成1957-11-30，这个日期早于当前日期，计算出来的年龄自然是正数（你得到61，应该是当前日期在2018年左右，逻辑完全一致）。

为什么修改NLS_DATE_FORMAT没变化？ #

你改完没效果，大概率是这两个原因之一：

• 改的格式还是用了RR/YY：比如你改成DD/MM/RR或者DD/MM/YY，本质还是沿用两位年份的模糊解析规则，没从根源上指定明确的世纪。
• 修改的作用域不对：如果你用ALTER SESSION SET NLS_DATE_FORMAT='...'只改了当前会话，后续换了会话执行SQL，或者你改的是系统级但没重启实例（一般不建议改系统级），都会导致设置不生效。

怎么彻底解决？ #

推荐两种靠谱的方式，从根源避免歧义：

1. 直接用四位年份的日期字符串：
   把日期写全，让Oracle不用猜世纪，比如：

   SELECT CEIL(MONTHS_BETWEEN(CURRENT_DATE, TO_DATE('30/11/1947', 'DD/MM/YYYY'))/12) AS AGE FROM DUAL;
   

2. 在TO_DATE里明确指定格式模型：
   哪怕用两位年份，也通过格式模型强制锁定世纪，比如：

   -- 强制解析为19xx年（RR格式结合当前年份规则会自动识别）
   SELECT CEIL(MONTHS_BETWEEN(CURRENT_DATE, TO_DATE('30/11/47', 'DD/MM/RR'))/12) AS AGE FROM DUAL;
   

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