Solved

Rounding floats in sql

Posted on 2004-10-22
3,499 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
Question by:SKM0211
    12 Comments
     
    LVL 13

    Accepted Solution

    by:
    Use build-in function:

    ROUND ( numeric_expression , length [ , function ] )

    Ex:

    SELECT ROUND(123.4545, 2) -- returns 123.4500

    0
     
    LVL 32

    Expert Comment

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

    by:Michael_D
    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
    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
    God Bless Microsoft %)
    0
     
    LVL 32

    Expert Comment

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

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    I recently came across an interesting Question In EE (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_27070749.html?cid=1135#a35876665) and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    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.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    846 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now