Conversion failed when converting the varchar value in case

0
votes

I am getting this error when I run the case statement below. I think I have to convert at some point, but not sure where or how.

Conversion failed when converting the varchar value 'reassign' to data type int.

,(CASE WHEN Mstime <=10 THEN CAST('reassign' as varchar(30) )
            WHEN Mstime >=10 THEN Mstime END ) as TimeGroup
2
sqlsql-server

2 Answers

2
votes

You need to cast() Mstime to varchar(30) if that is the datatype you want to return for that expression.

,(case when Mstime <=10 then cast('reassign' as varchar(30) ) 
      when Mstime >=10 then cast(Mstime as varchar(30) )
      end ) as TimeGroup
2
votes

Cast the else, not the then (it is already a string):

(CASE WHEN Mstime <= 10 THEN 'reassign'
      WHEN Mstime >= 10 THEN CAST(Mstime as varchar(30))
 END) as TimeGroup

The problem you are facing is that the ELSE returns an INT. By the rules of type compatibility, SQL Server attempts to have the CASE expression return an INT instead of a string.