Nested case statements Teradata converting timestamp(0),timestamp(6) to date and compare

Question

  I want to generate qualify_flag as '1' always when device_end_dt = '9999-12-31 23:29:59'. If it is 
 not '9999-12-31 23:29:59' then if DEVICE_END_DT + 1 day = CREATION_DATE then qualify_flag is 0 else 1. 
DEVICE_END_DT is TIMESTAMP(0), CREATION_DATE  is TIMESTAMP(6). 
CREATION_DATE column doesn't have any value which is '9999-12-31'.

Below case statement is failing with invalid date, seems like '9999-12-31' is coming somehow and ('9999-12-31' + 1) which is ambiguous.

select
case When 
        Cast(DEVICE_END_DT AS DATE) <> CAST('9999-12-31' AS DATE) 
            THEN
                case when CAST((Cast(DEVICE_END_DT AS DATE) + 1) AS DATE) = Cast(CREATION_DATE AS DATE)
                        then 0
                        else 1
                end

case when   
        Cast(DEVICE_END_DT AS DATE) = CAST('9999-12-31' AS DATE) 
            THEN
                            1 
end 
end as qualify_flag
 FROM ABC;

Answer 1

This is your logic simplified:

CASE 
  WHEN Cast(DEVICE_END_DT AS DATE) = Cast(CREATION_DATE AS DATE) - 1
  THEN 0
  ELSE 1
END

Btw, better use a date literal DATE '9999-12-31' instead of CAST('9999-12-31' AS DATE).

What is your final business question you're trying to solve? Looks like identifying bad data in a slowly changing dimension.