Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Rounding floats in sql

Posted on 2004-10-22
Medium Priority
3,529 Views
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

0
Question by:SKM0211
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 2
• 2
• +3

LVL 13

Accepted Solution

Michael_D earned 750 total points
ID: 12384168
Use build-in function:

ROUND ( numeric_expression , length [ , function ] )

Ex:

SELECT ROUND(123.4545, 2) -- returns 123.4500

0

LVL 32

Expert Comment

ID: 12384192
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

LVL 50

Expert Comment

ID: 12384204
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

LVL 13

Expert Comment

ID: 12386390

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

Thank you,

Michael

0

LVL 17

Expert Comment

ID: 12387602
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

LVL 13

Expert Comment

ID: 12390297
God Bless Microsoft %)
0

LVL 32

Expert Comment

ID: 12403605
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

LVL 13

Expert Comment

ID: 12417241
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

Expert Comment

ID: 12611444
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

LVL 50

Expert Comment

ID: 12612515
?

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

LVL 1

Expert Comment

ID: 13342834
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

LVL 13

Expert Comment

ID: 13351220
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this â€¦
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
###### Suggested Courses
Course of the Month8 days, 7 hours left to enroll