Getting Error: "Datatype Mismatch in THEN/ELSE expression"
from the below code: MAX(CASE WHEN CustRank = 1 THEN StopDt ELSE 0 END) AS StopDt1
Here StopDt is in MM/DD/YYYY format. Please help how to resolve the issue.
Asked
Active
Viewed 351 times
0
-
`0` is not a valid date, simply remove the ELSE to get either the max date or a NULL. – dnoeth Jan 26 '22 at 07:05
1 Answers
0
It depends on the data type of StopDt; I am assuming that it is DATE. IF so, the ELSE part must be a date, also. Zero (0) is not a valid date. You can use
MAX(CASE WHEN CustRank = 1 THEN StopDt ELSE CAST(NULL as DATE) END)
MAX will ignore the NULL values (ie CustRank<>1 cases) but it will it will return NULL if there is no record with CustRank=1. I assume this is acceptable
If you really want to show zero in those instances, you need to cast the entire expression to string (varchar) and then use COALESCE (or similar) to replace NULL values with '0'.
tinazmu
- 3,880
- 2
- 7
- 20