asked
Cast Error
I am trying to divide two columns and the result keeps coming back as 0.0000 when that isn’t the correct answer. I believe it is a cast error If I add the columns it produces the correct result the problem occurs when I divide. select colA, ColB, ((ColA/ColB)* 100.0000) AS Answer from tableA ColA ColB Answer 1 2 0.0000
Similar to your previous question?
http://ask.sqlservercentral.com/questions/80038/cast-to-decimal
Yes it is a cast/datatype 'error' You are doing **integer** division so 1/2 = 0 is the right answer If you cast the first column as a decimal, ensuring that you give enough precision for your data for example.... select cast(colA as decimal(8,2))/ColB * 100
This is because when an integer division takes place, 1/2 = 0. Once this happens, you then multiply it by 100.0000 but this is already too late. To prevent the integer division from happening, you can either cast colA or colB as a sutable decimal or multiply any one of them by 1.0 to get the same result, i.e. select colA, ColB, (cast(ColA as decimal(18,4))/ColB)* 100.0000) AS Answer from tableA; -- this should work as well select colA, ColB, (((ColA*1.0)/ColB)* 100.0000) AS Answer from tableA; Oleg
