Encountering Datatype Mismatch in THEN/ELSE Expression Error

UDA
N/A

Encountering Datatype Mismatch in THEN/ELSE Expression Error

Hi, I have this entry in my select statement:

[font=Courier New]select
case when a.exp_date = '2009/12/31'
then '991231'
else a.exp_date
end (date, format 'YYMMDD')(CHAR(6)),[/font]

The error I get is the 3800: Datatype Mismatch in THEN/ELSE Expression Error.

The format of the date in our tables is in this way : MM/DD/YYYY. Commenting out "else a.exp_date" gets rid of the error.

I don't understand why the else clause errors out.. Can anyone see where i went wrong?
2 REPLIES
Teradata Employee

Re: Encountering Datatype Mismatch in THEN/ELSE Expression Error

Try this:

select
case when a.exp_date = '12/31/2009'
then cast('991231' as date format 'yymmdd')
else cast(a.exp_date as date format 'yymmdd')
end

This string passed shoud be in the same format of the date being compared to and you need to explicitly convert the format if you need it to be different than the source.
N/A

Re: Encountering Datatype Mismatch in THEN/ELSE Expression Error

thanks Jitender Aluri! your reply really helped me come to the solution! :) I edited it a bit since when i tried it out somehow it doesn't get the expected result. After a bit of tweaking the statement, guided by the logic you've given, I am now able to get my desired result. This is what I used:

select top 100
case when a.exp_date = '2009/12/31'
then cast ('991231' as date format 'yymmdd')
else a.exp_date
end (date, format 'yymmdd')(char(6))

many thanks once again!