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
12
Medium Priority
?
3,529 Views
Last Modified: 2012-08-13
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?

Any ideas please?

Many thanks

0
Comment
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
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +3
12 Comments
 
LVL 13

Accepted Solution

by:
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

by:Brendt Hess
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

by:Lowfatspread
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:Michael_D
ID: 12386390
Lowfatspread :

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

Thank you,

Michael

0
 
LVL 17

Expert Comment

by:BillAn1
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

by:Michael_D
ID: 12390297
God Bless Microsoft %)
0
 
LVL 32

Expert Comment

by:Brendt Hess
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

by:Michael_D
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

by:raju1
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

by:Lowfatspread
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

by:kbrc
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

by:Michael_D
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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

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.

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question