正确提取月份与编写CASE WHEN语句：修复ORA-00920错误求助

错误原因 #

原语句的WHEN子句存在语法错误：EXTRACT(MONTH FROM APPROVED_DATE) AND EXTRACT(MONTH FROM END_DATE) BETWEEN X AND Y 这种写法不符合SQL语法规范，AND左侧的月份值未关联任何比较条件，导致数据库无法识别合法的关系运算符，触发ORA-00920错误。

修正方案1：修复原语法结构 #

将每个WHEN子句修改为两个月份分别满足季度范围，再用AND连接的形式：

CASE 
    WHEN EXTRACT(MONTH FROM APPROVED_DATE) BETWEEN 1 AND 3 
         AND EXTRACT(MONTH FROM END_DATE) BETWEEN 1 AND 3 THEN APPROVED_DATE
    WHEN EXTRACT(MONTH FROM APPROVED_DATE) BETWEEN 4 AND 6 
         AND EXTRACT(MONTH FROM END_DATE) BETWEEN 4 AND 6 THEN APPROVED_DATE
    WHEN EXTRACT(MONTH FROM APPROVED_DATE) BETWEEN 7 AND 9 
         AND EXTRACT(MONTH FROM END_DATE) BETWEEN 7 AND 9 THEN APPROVED_DATE
    WHEN EXTRACT(MONTH FROM APPROVED_DATE) BETWEEN 10 AND 12 
         AND EXTRACT(MONTH FROM END_DATE) BETWEEN 10 AND 12 THEN APPROVED_DATE
    ELSE END_DATE
END pitem_date 

修正方案2：简化季度判断逻辑 #

利用Oracle的TRUNC函数按季度截断日期，直接判断两个日期是否处于同一季度，代码更简洁高效：

CASE 
    WHEN TRUNC(APPROVED_DATE, 'Q') = TRUNC(END_DATE, 'Q') THEN APPROVED_DATE
    ELSE END_DATE
END pitem_date 

TRUNC(date, 'Q')会将日期截断到对应季度的第一天，比如2024-05-10会被截断为2024-04-01，只要两个日期的截断结果相同，就说明它们在同一个季度。

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