Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

 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]
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading