SQL Server 2005 : conversion failed when converting the varchar value '0.00' to data type int

2
votes

When adding this line into my query:

convert(varchar(20), convert(varchar(20),
sum(case when tsr.other like '%aa%' then tsr.block1 else 0 end) +
sum(case when tsr.other like '%aa%' then tsr.block2 else 0 end) +
sum(case when tsr.other like '%aa%' then tsr.block3 else 0 end) +
sum(case when tsr.other like '%aa%' then tsr.block4 else 0 end)) * 450)

I get this error message:

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

Data in block column is days - e.g. 10.0

Any ideas?

i've got it fixed, it was just changing the 450 to 450.0.

The reason for the varchars is that this is just 1 line from 1 of multiple unioned select statements.

2
sqlsql-serversql-server-2005
forget that, answered my own question. changed 450 to 450.0 and gets rid of the error message. - user1794118
Looks like you're trying to multiple an varchar(20) by 450 no? - Tr1stan
try like this ... then Cast(tsr.block2 as float) else 0.0 end - bummi
Why on earth are you summing a column, converting the result to a VARCHAR then multiplying it by 450??? Surely, surely, if anything you should be explicity casting strings to numerics, not the other way around? - MatBailie
No need to be wide mate, i'm just learning. - user1794118

2 Answers

1
votes

10.0 isn't an int - it's a decimal.

Try 

declare @i int 
select @i = convert(decimal(9,4),'10.0')
select @i

and the conversion from the decimal to the int will be done implicitly.

1
votes
 '10.0' isn't an int/decimal  - it's a varchar .   
 do any mathematical calculation only on decimal/numeric/float/int values.

 SELECT convert(varchar(20), convert(decimal(10,2), sum(case when
 tsr.other like '%aa%' then  convert(decimal(10,2),tsr.block1) else 0
 end) + sum(case when tsr.other like '%aa%' then 
 convert(decimal(10,2),tsr.block2) else 0 end) + sum(case when
 tsr.other like '%aa%' then  convert(decimal(10,2),tsr.block3) else 0
 end) + sum(case when tsr.other like '%aa%' then 
 convert(decimal(10,2),tsr.block4) else 0 end)) * 450)