Error Converting Date YYYYMMDD to MM/DD/YYYY

[MFisher]

We have dates stored as DECIMAL 8,0 in format YYYYMMDD. We have some "invalid" dates like 00010101 that fail converting to YYYYMMDD. So I tried to use a CASE to only convert valid dates to MM/DD/YYYY, but none of them display properly in the CASE statement (Invoice_Date2), but the same convert works fine when not in the CASE. ( Invoice_Date1 )

SELECT
VARCHAR_FORMAT(DATE(TIMESTAMP_FORMAT(CHAR(CTTDTE), 'YYYYMMDD')),'MM/DD/YYYY') as Invoice_Date1,
CASE WHEN CTTDTE < 19000101 then CTTDTE else VARCHAR_FORMAT(DATE(TIMESTAMP_FORMAT(CHAR(CTTDTE), 'YYYYMMDD')),'MM/DD/YYYY') END as Invoice_Date2,
CTTDTE as Invoice_Date3

So valid dates in CTTDTE display as ++++++++++ when in the CASE statement. Thoughts ?

[andreas.prouza]

One problem is the different data types:

CASE WHEN CTTDTE < 19000101 then CTTDTE

... should be ...

CASE WHEN CTTDTE < 19000101 then char(CTTDTE)

So that all results will be the same type (char)

[MFisher]

Good catch, Thanks