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

4
votes

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

3
sqlsql-serverstored-procedures
What is the Data Type of DI.Coupon? - DineshDB
varchar @dinesh - Aravindhan R
Please provide some sample data for the Coupon column. - Tim Biegeleisen
Coupon 0.0702, 0.0218, 0.008, 0.0169, 0.0259 0.0067 0.0134, 0.0156, 0.0183, 0.0252, 0.0351 - Aravindhan R

3 Answers

3
votes

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'',
3
votes

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
2
votes

It should be like this

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