Rounding floats in sql

Hi,
I've  a stored procedure that returns lots of calculations in floats, such as :
0.01709567124701632
0.02212760467066641
1.9891500904159132E-2 etc.

How do I round these to two decimal places like, .02, .02 etc..

I'm outputting the final result to Excel,so these should be passed as numbers?

Many thanks

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Use build-in function:

ROUND ( numeric_expression , length [ , function ] )

Ex:

SELECT ROUND(123.4545, 2) -- returns 123.4500

0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Senior DBACommented:
Here is an example using generic field names.  Try:

SELECT  Cast(Float1 As Numeric(8,2)) As NumericValue, Round(Float2, 2) as RoundFloat FROM MyTable

Either method works... the first will return a guaranteed precise value, the second will have the standard limitations on precision of Float numbers.

0
Commented:
you should probably convert the floats to a standard decimal  and round with that...

e.g.
Convert(decimal(8,2),round(convert(decimal(9,3),ColumnName),2))

0
Commented:

Why so complicated?
why not just ROUND(ColumnName,2)? Is this method has limitations?

Thank you,

Michael

0
Commented:
ROUND returns the same data type as it is passed, so if it is passed a float, it will return a float.
This may result in insternal storage errors resulting in the number nolonger being cleanly rounded.
As an example, try this :

declare @f float
set @f = 1.0/7.0

select round(@f,3)

result :
0.14299999999999999

i.e. it rounded to 0.143, but then converted back to float and it became 0.14299999999999999

in order to keep the rounded numbers clean, you need to convert to decimal.

0
Commented:
God Bless Microsoft %)
0
Senior DBACommented:
Actually, this is an intrinsic limit in floatin-point numbers in computers.  Oracle has the same issue, as does everyone else.

See http://support.microsoft.com/kb/q42980/ for a good explanation of why this happens in detail.  Short version:  We are trying to represent a base 10 number in base 2, and this can lead to infinitely repeating numbers (e.g. in binary, 1/10 (0.1) is a repeating value, with 1100 repeated infinitely.)
0
Commented:
SKM0211:
Thanks for accepting my comment as an answer, but after reading comments of bhess1 and BillAn1 I am trying to review my own code to find out where the rounding error of Round() function might be critical and change it to Convert()

Thank you, guys!!
0
Commented:
Hi experts,

SELECT round(CAST(380 as float)/CAST(1121 as float),2)
--returns 0.34000000000000002
It should be 0.34000000000000000. Could I know why this ambiguity?

SELECT round(0.33898305084745761, 2)
--returns .34000000000000000

Pls help.
0
Commented:
?

float is an imprecise numeric type,
try using Numeric(p,q) or Decimal(p,q)
and you should get the 0.34 result always...
0
Commented:
Hi,

Use the following technique :-
==================
DECLARE
@num FLOAT

SET @num = 233.23609
PRINT ROUND(@num, 2))

===================

Regards,
K B Roy Choudhuri,
CTS, Kolkata, INDIA
kbrc@rediffmail.com
0
Commented:
kbrc :
Congrats, you managed to find the right answer even less then in 6 month after the question was PAQed :)
That was realy impressive! I would like to split the points I got with you if it possible :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.