Help with ROUND function showing too many decimals

[bmacbmac]

Hi Everyone,

I have a percentage column I am trying to round to two decimal places. If I try:

select percentage, round(percentage, 2) from my table

I get the following results:
21.886792452830 21.890000000000001
19.245283018867 19.25
13.584905660377 13.58
14.716981132075 14.720000000000001


Can anyone tell me why the 14. and the 21. values will not round to two decimal places? Or tell me how to get around this?

Thanks!

Brian

 

[gmmastros]

[blue]Can anyone tell me why the 14. and the 21. values will not round to two decimal places?[/blue]

I suspect this is because your data type for the percentage column is float (or real). Those data types are considered "approximate' numbers. It's complicated but is caused by the internal representation of numbers. For example, 2 / 3 = 0.66666 (repeating).

[blue]Or tell me how to get around this?[/blue]

This is easy enough to accomplish by converting the data type to a decimal with the precision you need.

select percentage, Convert(Decimal(5,2), percentage) from mytable

Decimal 5,2 can store a number between -999.99 to positive 999.99. 5,2 means 5 total digits with 2 of them after the decimal point. This is the appropriate data type for percentages which have an effective range between 0 and 100. If you used decimal 4,2, the highest acceptable percentage would be 99.99 % (instead of 100).

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bmacbmac]

Ahhhh... I've been fighting this forever. Thanks! Worked great!