Rounding issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Rounding issue

Author

Topic

WindChaser
Posting Yak Master

225 Posts

Posted - 2008-12-29 : 18:12:51

Hi folks.

A bit of a rounding issue. This is my statement

Select ID, Amount, Round(Amount,2) as RoundedAmount from Transactions where ID = 7

and it returns

ID Amount RoundedAmount
7 7.69 7.6899999999999995

Can anyone tell me what's going on?

Thanks!

revdnrdy
Posting Yak Master

220 Posts

Posted - 2008-12-29 : 18:56:08

Well at a glance the syntax looks ok. I would presume you may wish to check your datatype. Perhaps you need to look at CAST or CONVERT functions in sql?

Otherwise the syntax of the code is correct. It seems to depend on what the datatype of field 'amount' is.

Hard to believe that the code would add precision since the original value of amount is 7.69 in your example.

r&r

WindChaser
Posting Yak Master

225 Posts

Posted - 2008-12-29 : 19:32:14

Data type of the Amount column is real. How 'bout this:

Select ID, Amount, Round(Amount,2) as RoundedAmount from Transactions where Amount <> Round(Amount,2)

yields the following partial results

ID Amount RoundedAmount
1 85.6 85.6
3 250.4 250.4
4 92.63 92.63
11 92.8 92.8
12 73.6 73.6
13 289.6 289.6
14 523.2 523.2
16 437.2 437.2
21 370.56 370.56
28 266.02 266.02
29 200.63 200.63

What the heck is going on??????????

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-29 : 19:32:53

Change your data type to DECIMAL. Real/float should not be used where accuracy matters.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

WindChaser
Posting Yak Master

225 Posts

Posted - 2008-12-29 : 20:21:13

Well, of course you're right as usual Tara. I performed the query by casting as decimal and everything works fine. But I still can't swallow the fact that Round(7.69, 2) = 7.6899999999999995 . I don't care what type the field is, that's just plain wrong.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-12-30 : 00:02:04

quote:
Originally posted by WindChaser

Well, of course you're right as usual Tara. I performed the query by casting as decimal and everything works fine. But I still can't swallow the fact that Round(7.69, 2) = 7.6899999999999995 . I don't care what type the field is, that's just plain wrong.

No, it is not wrong. You have to realize that REAL and FLOAT are binary numbers, and decimal fractions do not always have an exact representation as binary fractions, so it may be displayed as a repeating decimal.

If you are handling ordinary amounts, use DECIMAL data types, or you will be dealing with this issue all the time. Use REAL and FLOAT for scientific calculations or with calculations that use SQL Server trigonometric or math functions.

CODO ERGO SUM

WindChaser
Posting Yak Master

225 Posts

Posted - 2008-12-30 : 09:07:41

quote:
Originally posted by Michael Valentine Jones

No, it is not wrong. You have to realize that REAL and FLOAT are binary numbers, and decimal fractions do not always have an exact representation as binary fractions, so it may be displayed as a repeating decimal.
CODO ERGO SUM

Understood, thanks for that. But I still can't wrap myself around the fact that the Round function can completely disregard the specified limit of digits. If I specify Round(7.69, 2), isn't it inherent to the function to limit itself to 2 digits?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-12-30 : 09:45:01

quote:
Originally posted by WindChaser

quote:
Originally posted by Michael Valentine Jones

No, it is not wrong. You have to realize that REAL and FLOAT are binary numbers, and decimal fractions do not always have an exact representation as binary fractions, so it may be displayed as a repeating decimal.
CODO ERGO SUM

Understood, thanks for that. But I still can't wrap myself around the fact that the Round function can completely disregard the specified limit of digits. If I specify Round(7.69, 2), isn't it inherent to the function to limit itself to 2 digits?

The round function rounds the value down as requested, but since the output data type is FLOAT, it has to convert it to a FLOAT value that is the closest possible representation in binary.

As I said before, you should avoid FLOAT and REAL for ordinary quantities like this.


select
	x,
	xr = round(x,2),
	xrr = round(convert(decimal(10,2),x),2)
from
	(
	select x = convert(real, 7.69)
	) a

Results:
x                        xr                                                    xrr          
------------------------ ----------------------------------------------------- ------------ 
7.6900001                7.6899999999999995                                    7.69

(1 row(s) affected)

CODO ERGO SUM

WindChaser
Posting Yak Master

225 Posts

Posted - 2008-12-30 : 10:48:15

I've converted all relevant columns. Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources