I am very curious, not sure if I missed something here.
But using Cast (yyMMdd) in a case statement throws an error
‘Conversion failed when converting date and/or time from character string.’
But in a single select like below, it returns the result
select case
when ISDATE('000000') = 0 then 'False'
else CAST('950705' as date)
end [YEAR]--error on else
select CAST('950705' as date) [Year]-- 1995-07-05 (works)
>Solution :
The error is not on the else but on the then 'false' – due to datatype precedence and that case can only return a single datatype. you’re trying to resturn a date and a string.
use try_convert or try_cast
select try_cast('000000' as date)
,try_cast('950705' as date) as [YEAR]