时间戳

首页 > 解决方案 > ORA-01861: 文字与 oracle 中 case 语句的格式字符串不匹配

oracle - ORA-01861: 文字与 oracle 中 case 语句的格式字符串不匹配

问题描述

请帮我纠正这个错误我不断收到这个错误

ORA-01861: 文字与 oracle 中 case 语句的格式字符串不匹配

SELECT activity_made,
       (CASE
            WHEN (TO_DATE(activity_made, 'DD-Mon-YYYY')) = TRUNC(SYSDATE) THEN TO_CHAR(activity_made, 'hh12:mi PM')
            WHEN TRUNC(TO_DATE(activity_made, 'MM-DD-YYYY')) BETWEEN TRUNC(SYSDATE, 'yy') AND TRUNC(SYSDATE - 1) THEN TO_CHAR(activity_made, 'Mon dd')
            ELSE TO_CHAR(TO_DATE(activity_made, 'MM/DD/YYYY'), 'mm/dd/yyyy')
        END)
           AS actmode
  FROM (SELECT (CASE
                    WHEN (SELECT COUNT(*)
                            FROM sfa_activity sa
                           WHERE     sa.companyid = opp.companyid
                                 AND sa.opptyid = opp.opptyid
                                 AND sa.TYPE = 'NOTE') > 0
                    THEN
                        (SELECT NVL(MAX(TO_CHAR(sa.updat, 'YYYY/MM/DD HH24:MI:SS')), '0000/00/00 00:00:00')
                           FROM sfa_activity sa
                          WHERE     sa.companyid = opp.companyid
                                AND sa.opptyid = opp.opptyid
                                AND sa.TYPE = 'NOTE')
                    ELSE
                        (SELECT NVL(MAX(TO_CHAR(sa.updat, 'YYYY/MM/DD HH24:MI:SS')), '1001/01/01 01:01:01')
                           /*ELSE (SELECT (MAX(TO_CHAR(sa.updat,'YYYY/MM/DD HH24:MI:SS')))*/
                           FROM sfa_activity sa
                          WHERE     sa.companyid = opp.companyid
                                AND sa.opptyid = opp.opptyid
                                AND sa.TYPE <> 'NOTE')
                END)
                   AS activity_made
          FROM sfa_opportunities opp
         WHERE companyid = 1192)

标签： oracledatedatetime

解决方案

似乎ACTIVITY_MADE是yyyy/mm/dd hh24:mi:ss格式（如内联视图所建议的那样）。如果简化，您的查询看起来像

select to_date(activity_made, ...) --> should use the same format as below, in an inline view
from (select activity_made         -->TO_CHAR(sa.updat, 'YYYY/MM/DD HH24:MI:SS')
      from sfa_opportunities)

所以：如果你使用了那个格式掩码activity_made，你应该TO_CHAR在main SELECT中使用它。但是，您使用

  • dd-mon-yyyy
  • mm-dd-yyyy
  • 月/日/年

切换yyyy/mm/dd hh24:mi:ss到CASE.

推荐阅读