如何修复sql ora-01843不是有效月份的情况

Question

运行sql脚本后，出现日期错误“ORA-01843 not a valid month”，日期格式为（mm/dd/yyyy）这在plsql上发生，但我不知道请看下面的sql语句

SELECT COUNT(*) FROM 
(SELECT c1.end_date AS eddates,cc.effectiveto,
CASE WHEN cc.effectiveto >= '1/1/4000' THEN  '1/1/4000'
     WHEN cc.effectiveto is null THEN  '1/1/4000'
     WHEN cc.effectiveto < '1/1/4000' THEN to_char(cc.effectiveto)
  END mock_effectiveto 
from (select aa.*,b.perm_id
      from smf2.security_xref aa,
           smf2.t_security_source b
      where identifier_type_id = 4862
      and aa.security_id = b.security_id
      and b.data_source_id = 52992
      AND aa.data_source_id = 52985)c1,

      (Select bc.* 
      FROM ccc_bimbqm.instrument ab , ccc_bimbqm.identifier bc  
      WHERE bc.identifiertypepermid = 320015  
      AND ab.instrumentpermid = bc.objectpermid
      AND bc.objectpermid IN 
      (SELECT bondid FROM ccc_muni.munibonds)) cc
WHERE  c1.perm_id = cc.objectpermid
and c1.start_date = cc.effectivefrom
and cc.identifiervalue is not null
and c1.code is not NULL ) ax
WHERE ax.eddates <> mock_effectiveto 

Answer 1

您可以将CASE语句更新为-

CASE WHEN cc.effectiveto = TO_DATE('01/01/4000', 'MM/DD/YYYY') OR cc.effectiveto is null
          THEN TO_DATE('01/01/4000', 'MM/DD/YYYY')
     WHEN cc.effectiveto < TO_DATE('01/01/4000', 'MM/DD/YYYY')
          THEN to_char(cc.effectiveto)
END mock_effectiveto