CODEWITHSUNDEEP
sql-server
Srinivasan
Srinivasan

Reputation: 12060

Conversion failed when converting the varchar value 'No' to data type tinyint

I have one SQL server table which has one tinyint column. The value of the column will be 0 or 1 or 2.

  If it is "0", I have to show "No". 
  If it is "1", I have to show "Yes". 
  If it is 2, it needs to show just 2.

Below is my Query,

select 
case when flag=1 then 'Yes'
when flag=0 then 'No' 
else flag 
end flag
from Employee

When I execute this query I'm getting below exception,

Conversion failed when converting the varchar value 'Yes' to data type tinyint.

Upvotes: 1

Views: 11531

Answers (2)

StackUser
StackUser

Reputation: 5398

Your Else part is of type TinyInt other case statements return Varchar. This causes the issue. You can get rid of the issue by using the below.

(The SQL is similar to Juergen post but with minor change)

select case when flag=1 then 'Yes'
            when flag=0 then 'No' 
            else CAST(flag AS Varchar(3)) 
       end flag
from Employee

The size of Tiny Int is 0-255 so we can convert to Varchar(3)

Upvotes: 0

juergen d
juergen d

Reputation: 204904

You can't mix the data types in a case. Cast the number output to a string

select case when flag=1 then 'Yes'
            when flag=0 then 'No' 
            else CAST(flag AS CHAR(1)) 
       end flag
from Employee

Upvotes: 3

Related Questions