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

Question

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.

Tag the RDBMS you are really using. SQL Server and MysQL are completely different. The error, however, is telling you the problem, '7.350000' isn't a valid int value; integers don't have decimal places. — Larnu
If this is SQL Server, you might want to look into try_convert — James Z

Gordon Linoff Gordon Linoff · Accepted Answer · 2021-09-03T15:14:40

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;

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

1 Answers