SQLTeam.com | Weblogs | Forums

Problem with rounding


#1

Hello,

I have a problem with rounding values.
For example:
Value 1.2625 must result in 1.263 (it now results in 1.262)
And value 1.2624 must result in 1.262

with other words, from 5 up to 9 at the fourth decimal must round the third decimal up
and from 0 to 5 at the fourth decimal it must round the third decimal down.

I use the following query:
select round (price,3) from articles

Thank you in advance.


#2

select round (price + 0.0005,3,1) from articles


#3

Thanks, but i still get 1.262 instead of 1.263


#4

This is a "feature". An annoying one for sure. FLOAT is an approximate representation and so what is represented internally may be close to 1.2625, but not exactly 1.2625. It is a tiny delta less than 1.2625. So when you round it, it rounds down.

You can run the following queries to see this behavior. This does not mean that you can always add 1.0E-10 and get the correct rounded results. Doing so might cause the data to get rounded the wrong way in some other cases.

DECLARE @Price1 FLOAT = 1.2625;
SELECT ROUND(@Price1,3);

DECLARE @Price2 DECIMAL(18,10) = 1.2625;
SELECT ROUND(@Price2,3);

DECLARE @Price3 FLOAT= 1.2625 + 1.0E-10;
SELECT ROUND(@Price3,3);

#5

select round (cast(price as decimal(13, 4)) + 0.0005,3,1)