CODEWITHSUNDEEP
sql
user16290409
user16290409

Reputation: 11

Conversion failed when converting the varchar to numeric and displaying numeric values only

select * 
    ,iif (DAYSS>=1, DAYSS, 'Out of Stock') as NEWDA
    from TABLE3

got an error

converting data type varchar to numeric.

when tried to convert numeric to varchar but that is also an error

use case when isnumeric (DAYSS) >=0 then CAST(DAYSS as varchar)

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

Upvotes: 0

Views: 185

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

I strongly recommend case instead of iif(), but it is the same problem. The expression refers to a single value, with a single type. If one branch ("then") is a number and the other a string ("else"), then the result is a number.

So, convert the number to a string:

select t3.*,
       (case when DAYSS >= 1 then cast(DAYSS as varchar(255))
             else 'Out of Stock'
        end) as NEWDA
from TABLE3 t3;

Upvotes: 1

Related Questions