oracle Oralce错误：文字与格式字符串不匹配

bfnvny8b 于 2023-06-22 发布在 Oracle

关注(0)|答案(1)|浏览(135)

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 BNFT_STA_DT >= TRUNC(NEXT_DAY(SYSDATE-7, 'monday')) AND 
                                      BNFT_END_DT < TRUNC(NEXT_DAY(SYSDATE, 'monday')+1)
                           ) > 0 
                    THEN 'case 1'
                    ELSE 'case 2'
                END 
               ELSE 'case 3'
          END AS CMMT
   FROM ( SELECT 
                 SYSDATE + (1 - TO_CHAR(SYSDATE,'D'))+ 1 AS this_week_monday
               , SYSDATE + (1 - TO_CHAR(SYSDATE,'D'))+ 7 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 BNFT_STA_DT >= TRUNC(NEXT_DAY(SYSDATE-7, 'monday')) AND 
                 BNFT_END_DT < 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
;

错误“文字与格式字符串不匹配”继续发生。'BNFT_STA_DT'和'BNFT_END_DT'是varchar 2类型。我认为sysdate和next_day函数有问题。我不知道在哪里或如何修复它。救命啊！

oracle

来源：https://stackoverflow.com/questions/76439550/oralce-error-literal-does-not-match-format-string

1条答案

按热度按时间

pkbketx91#

您可以使用TO_DATE将字符串转换为日期（并且不应将日期存储在VARCHAR2字段中），并且可以使用TRUNC(SYSDATE, 'IW')查找当前星期一的午夜。
例如，如果日期数据以YYYY-MM-DD HH24:MI:SS存储，则：

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 SYSDATE < TRUNC(SYSDATE, 'IW') + INTERVAL '9' HOUR
       THEN 'Y'
       ELSE 'N'
       END AS GRD_CLT_YN 
     , CASE
       WHEN SYSDATE < TRUNC(SYSDATE, 'IW') + INTERVAL '9' HOUR
       THEN CASE
            WHEN ( SELECT COUNT(*) 
                   FROM   TB_BRND_BNFT
                   WHERE  TO_DATE(BNFT_STA_DT, 'YYYY-MM-DD HH:MI:SS') >= TRUNC(SYSDATE, 'IW')
                   AND    TO_DATE(BNFT_END_DT, 'YYYY-MM-DD HH:MI:SS') <  TRUNC(SYSDATE, 'IW') + 8 ) > 0 
            THEN 'case 1'
            ELSE 'case 2'
            END 
       ELSE 'case 3'
       END AS CMMT
FROM   ( SELECT TRUNC(SYSDATE, 'IW') AS this_week_monday
              , TRUNC(SYSDATE, 'IW') + 6 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 HH:MI:SS') >= TRUNC(SYSDATE, 'IW')
         AND    TO_DATE(BNFT_END_DT, 'YYYY-MM-DD HH:MI:SS') <  TRUNC(SYSDATE, 'IW') + 8
       ) 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
;

赞(0）回复(0）举报 2023-06-22

我来回答

oracle Oralce错误：文字与格式字符串不匹配

1条答案

相关问题

热门标签

最新问答