SQL & PL/SQL

Hello

I wanted to open a new discussion because this is an issue with converting my date fields stored as (INT) '20171005' to date '10/05/2017'

I'm using to_char(to_date(CV.DATEEFF, 'yyyy/mm/dd'), 'mm/dd/yyyy') to convert a number to date but my case statement is not working (See sample).

Any ideas?

My query:

SELECT

P.PRODUCERNO AS "ID",

TERMINATIONREASONCODE AS "TERM CODE", 

to_char(to_date(DateTERM, 'yyyy/mm/dd'), 'mm/dd/yyyy') AS "LIC Expiration",

to_char(to_date(E.COMPLETIONDATE, 'yyyy/mm/dd'), 'mm/dd/yyyy') AS "trAINING DATE",

to_char(to_date(CV.DATEEFF, 'yyyy/mm/dd'), 'mm/dd/yyyy') AS "STATUS EFF",

CASE

WHEN CV.TERMINATIONREASONCODE = 'COMPLETE' AND LV.DATETERM > to_char(current_date,'YYYYMMDD')

  THEN to_char(greatest(to_char(to_date(CV.DATEEFF, 'yyyy/mm/dd'), 'mm/dd/yyyy'),to_char(to_date(E.COMPLETIONDATE, 'yyyy/mm/dd'), 'mm/dd/yyyy')))

ELSE 'NOT ELIGIBLE TO SELL'

END AS "ELIGIBLE TO SELL STATUS"

  FROM PRODUCER P

INNER JOIN PRODUCERVER PV ON PV.PRODUCERNO = P.PRODUCERNO

INNER JOIN Contract C on C.ProducerNo = P.ProducerNo

INNER JOIN ContractVer CV on CV.ContractNo = C.ContractNo

LEFT JOIN License L on L.ProducerNo = P.ProducerNo

LEFT JOIN LicenseVer LV on LV.LicenseNo = L.LicenseNo

LEFT JOIN Education E on E.ProducerNo = P.ProducerNo

INNER JOIN JURISDICTION juris ON L.jurisdictionno = juris.jurisdictionno

WHERE CV.PROSTA = 1 AND CONTRACTTYPE IN ('Selling Agent', 'Producer') AND L.JURISDICTIONNO = E.JURISDICTIONNO

  AND PV.PROSTA = 1

  AND LV.PROSTA = 1

  AND E.BUSINESSUNIT = 'CENT' AND EDUCATIONCATEGORY = 'Product'

  AND AGREEMENT = '2019' AND to_date(cv.DateExp, 'yyyymmdd') > SYSDATE AND to_date(Lv.DateExp, 'yyyymmdd') > SYSDATE

Database Product

Database Product Version