decimal point in SQL Server

Question

I tried to do a calculation in SQL Server and to retrieved value with 2 decimal point. However, this is always return me the value in 0 for the 2 decimal point. Does anyone know what is going on there?

example:

select convert(decimal(5,2),((2*100)/19), 2) as 'test'
from customer

The return is always 10.00 instead of 10.53

Answer 1

Try changing one of the integers to a floating point number:

SELECT convert(decimal(5,2),((2*100.0)/19), 2) as 'test'
FROM customer

Result:

10.53

I picked 100 to change to 100.0 because I am guessing you are trying to calculate a percentage and that 100 will be a constant hardcoded in your final query. I am guessing that the other two values (2 and 19) will be later changed to read from integer fields in your table.

Answer 2

Try to divide by 19.0 If you just divide by 19, sql server assumes all the data types are integers and will do math accordingly.

Answer 3

Try this:

select convert(decimal(5,2),((20*100)/19.0), 2) as 'test'
from customer

That little ".0" at the end of your denominator should help.