SQL Cast as Date (yyMMdd) does not work in a case statement but works in a select-CodePudding

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)

CodePudding user response：

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]