Post reply

Not Rounding

  • July 28, 2003 at 8:38 pm

    #82036

    Here are three statements

    1.SELECT A.RetailPrice * F.salesQuantity FROM StockDept A .....

    Result 29.989999999999998

    2. select round(29.989999999999998,2)

    Result 29.990000000000000

    3.SELECT round(A.RetailPrice * F.salesQuantity,2)

    FROM StockDept

    result 29.989999999999998

    As you could see the third one should give the same result as 2nd one. But 3rd statement result is unrounded.

    Can any one tell me why?

    Awaiting ur reply

    Mani

  • July 28, 2003 at 9:15 pm

    #467384

    The result of A.RetailPrice * F.salesQuantity might be a FLOAT datatype, which won't round properly.

    What are the results of:

     

    select  sql_variant_Property(round(29.989999999999998,2),'BaseType')

    select sql_variant_Property(round(A.RetailPrice * F.salesQuantity,2),'BaseType')

    Well, I know what the first one is... DECIMAL. But I'm interested in the second. If it's FLOAT then you'll need to CAST as DECIMAL.

    I guess the ROUND does what you want, but because the result is a FLOAT the displayed value is the closest thing to 29.990000000000000 that a FLOAT can get.

    Interestingly, if you execute:

    select CAST(29.990000000000000 AS FLOAT)

    the result is:

    29.989999999999998


    Cheers,
    - Mark

  • July 28, 2003 at 9:31 pm

    #467386

    HI

    RetailPrice is money and SalesQuantity is float.

    I have done wht you said, that is i cast it as money and things works well. However two doubts.

    1.When I multiply Retailprice and salesQuantity, why is the result float instead of money?

    2.select CAST(29.990000000000000 AS FLOAT)

    the result is:

    29.989999999999998. This is bit strange to me. If this is the case how can i force sql to take 29.99 as such as float?. 29.99 is a valid float

  • July 28, 2003 at 9:42 pm

    #467387

    You get FLOAT as a result because of SQL Data Type Precedence (Look up "Data Type Precedence" in BOL).

    29.99 is a valid decimal, and is a valid value that can be assigned to a FLOAT. But, because a FLOAT can store huge values, the trade-off is a loss of precision. Therefore you can assign 29.99 to a FLOAT but it cannot store that precise value and thus stores the closest thing it can. Try...

     

    declare @x float

    set @x = 29.99

    SELECT @x

    Result will be:

    29.989999999999998

    Edited by - mccork on 07/28/2003 9:42:51 PM


    Cheers,
    - Mark

  • July 28, 2003 at 11:15 pm

    #467391

    Just for interest sake from BOL 2000 :

    DECIMAL AND NUMERIC

    Precision Storage bytes

    1 - 9 5

    10-19 9

    20-28 13

    29-38 17

    FLOAT AND REAL

    n is Precision Storage size

    1-24 7 digits 4 bytes

    25-53 15 digits 8 bytes

  • July 28, 2003 at 11:51 pm

    #467394

    Hi Mani,

    quote:

    Here are three statements

    1.SELECT A.RetailPrice * F.salesQuantity FROM StockDept A .....

    Result 29.989999999999998

    2. select round(29.989999999999998,2)

    Result 29.990000000000000

    3.SELECT round(A.RetailPrice * F.salesQuantity,2)

    FROM StockDept

    result 29.989999999999998

    as was written before, try to avoid float data type in calculations. Use decimal instead. Especially when you do more complex calculations or multiple step calculation where the input of the next step are the results of the preceeding step. Anyway, in your example if you round to 2 decimal places nobody will recognize a difference because all three alternatives result in 29,99, but consider what might happen when someone buys not just one or two articles but +20 or more. So each impreciseness will sum up till the end result is simply wrong.

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • July 29, 2003 at 12:35 am

    #467398

    HI frank,

    Are u saying that he best solution is to change the datatype to decimal to get high precision.

  • July 29, 2003 at 12:40 am

    #467400

    Hi Mani,

    quote:

    Are u saying that he best solution is to change the datatype to decimal to get high precision.

    yes, that's what I'm saying

    Maybe this one will also help you

    http://qa.sqlservercentral.com/forum/link.asp?TOPIC_ID=14515

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • July 29, 2003 at 12:42 am

    #467401

    I know I would

    Cheers,

    - Mark


    Cheers,
    - Mark

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply