Oracle报错：literal does not match format string，SQL语句修复求助

错误核心原因 #

你碰到的literal does not match format string错误，本质是字符串与日期的隐式转换失败：

• BNFT_STA_DT和BNFT_END_DT是varchar2字符串类型，但你直接和TRUNC(NEXT_DAY(...))这类日期类型的值做比较。
• Oracle会自动尝试把字符串转成日期，但如果字符串的存储格式和当前会话默认的日期格式不匹配（比如默认是DD-MON-RR，但你的字段存的是YYYY-MM-DD），就会触发格式不匹配的错误。

修复步骤 #

1. 确认日期字符串格式 #

先搞清楚BNFT_STA_DT和BNFT_END_DT的实际存储格式，比如常见的YYYY-MM-DD、DD/MM/YYYY、MM/DD/YYYY等。

2. 显式转换字符串为日期 #

所有涉及这两个字段的比较逻辑，都用TO_DATE函数显式转换，彻底避免隐式转换带来的问题。

3. 优化本周日期计算逻辑 #

原TB_A中用TO_CHAR(SYSDATE,'D')计算本周一的方式，依赖NLS地区设置（不同地区一周的起始日可能不同），改用NEXT_DAY更稳定可靠。

修改后的完整SQL #

SELECT 
          TB_A.this_week_monday 
        , TB_A.this_week_sunday 
        , TB_B.IMG_LCL_NM 
        , TB_B.IMG_NM 
        , TB_B.IMG_URL 
        , TB_B.EPS_SEQ 
        , TB_C.GR_NM
        , TB_C.GR_CL_CD 
        , TB_C.MBR_NO
        , CASE WHEN TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') = 'MON' AND TO_CHAR(SYSDATE, 'HH24:MI:SS') BETWEEN '00:00:00' AND '08:59:59' 
               THEN 'Y' ELSE 'N' END AS GRD_CLT_YN 
        , CASE WHEN TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') = 'MON' AND TO_CHAR(SYSDATE, 'HH24:MI:SS') BETWEEN '00:00:00' AND '08:59:59' 
               THEN CASE WHEN (SELECT COUNT(*) 
                                 FROM TB_BRND_BNFT
                                WHERE TO_DATE(BNFT_STA_DT, 'YYYY-MM-DD') >= TRUNC(NEXT_DAY(SYSDATE-7, 'MONDAY')) AND 
                                      TO_DATE(BNFT_END_DT, 'YYYY-MM-DD') < TRUNC(NEXT_DAY(SYSDATE, 'MONDAY')+1)
                           ) > 0 
                    THEN 'case 1'
                    ELSE 'case 2'
                END 
               ELSE 'case 3'
          END AS CMMT
   FROM ( SELECT 
                 TRUNC(NEXT_DAY(SYSDATE - 7, 'MONDAY')) AS this_week_monday
               , TRUNC(NEXT_DAY(SYSDATE, 'MONDAY')) AS this_week_sunday
            FROM DUAL
        )TB_A
      , ( SELECT 
                 IMG_LCL_NM 
               , IMG_NM 
               , IMG_URL 
               , B.EPS_SEQ 
            FROM TB_BRND_MGMT A JOIN TB_BRND_BNFT B ON A.GRP_MCHT_NO = B.GRP_MCHT_NO
           WHERE TO_DATE(BNFT_STA_DT, 'YYYY-MM-DD') >= TRUNC(NEXT_DAY(SYSDATE-7, 'MONDAY')) AND 
                 TO_DATE(BNFT_END_DT, 'YYYY-MM-DD') < TRUNC(NEXT_DAY(SYSDATE, 'MONDAY')+1)
        ) TB_B
    , (
       SELECT 
               GR_NM 
         , TB_MBR_GR.GR_CL_CD 
                 , APLY_STA_DT 
         , MBR_NO 
        FROM TB_MBR_GR 
        LEFT JOIN TB_MBR_GR_SET 
                ON TB_MBR_GR.GR_CL_CD = TB_MBR_GR_SET.GR_CL_CD
        WHERE MBR_NO = '2000000001'
        ORDER BY APLY_STA_DT DESC 
        FETCH FIRST 1 ROWS ONLY
       ) TB_C
;

额外注意 #

• 把TO_DATE里的格式字符串'YYYY-MM-DD'替换成你实际的字段存储格式。
• 如果字段里存在无效的日期字符串（比如'2023-02-30'），可以用VALIDATE_CONVERSION函数过滤，避免转换报错：

  WHERE VALIDATE_CONVERSION(BNFT_STA_DT AS DATE, 'YYYY-MM-DD') = 1
    AND TO_DATE(BNFT_STA_DT, 'YYYY-MM-DD') >= ...
  

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