Conversion failed when converting the varchar value int to data type int

user_8817802 0
Viewed 4651

I want to multiple the value with 100 in my sql statement. But it showing error like Conversion failed when converting the varchar value 0.0035 to data type int.

And i know that the value is in varchar. But i want to multiple with 100

this is my original query code:

@WalCalc as VARCHAR(20) = NULL,
Set @SQLQuery = @SQLQuery + 'CAST(   (ISNULL(DI.Coupon,0)) AS varchar(50)) AS ''Coupon'',

and the converted query

Set @SQLQuery = @SQLQuery + 'CAST(   (ISNULL(DI.Coupon,0) * 100) AS Decimal(18,3)) AS ''Coupon'',

Please some one help me

stored-procedures sql-server sql
3 Answers

I think you should be converting the Coupon column to a decimal first, and then multiplying by 100:

Set @SQLQuery = @SQLQuery + 'ISNULL(CAST(DI.Coupon AS Decimal(18, 3)), 0.0) * 100 AS ''Coupon'',
user_18632290

Try this,

First CAST your Coupon value to NUMERIC and do the Multiply:

Set @SQLQuery = @SQLQuery + 'CAST(   (ISNULL(CAST(DI.Coupon AS NUMERIC(18,3)),0) * 100) AS Decimal(18,3)) AS Coupon

This is What I Executed:

SELECT CAST((ISNULL(CAST('0.0702' AS NUMERIC(18,5)),0) * 100) AS Decimal(18,3)) AS Coupon

Output:

Coupon
7.020
user_38765650

It should be like this

Set @SQLQuery = @SQLQuery + 'CAST((ISNULL(DI.Coupon,0) ) AS Decimal(18,3)) * 100 AS ''Coupon'',
user_16456000
Related