CODEWITHSUNDEEP
sqloracle-database
Seaal
Seaal

Reputation: 49

how to fix this case sql ora-01843 not a valid month

After run sql scripts ,There's date error "ora-01843 not a valid month ", date format is (mm/dd/yyyy) this occurs on plsql, but I dont realise please see SQL statement below

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

Upvotes: 0

Views: 257

Answers (1)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You can update your CASE statement as -

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

Upvotes: 0

Related Questions