Post reply

Problems caused in computation of values due to Float and Decimal datatype

  • May 7, 2008 at 6:54 am

    #186292

    Hi,

    I am facing problems in computing the results which include sequence of arthmertic operations like multiplication and division on decimal and float datatypes.

    I am have to do some caliculations which are as below

    declare @revFundvalue1 decimal(25,14)

    declare @Fundvalue2 decimal(25,14)

    declare @Fundvalue3 decimal(25,14)

    declare @MulConstant1 float

    Set @revFundvalue1 = 1.258

    Set @Fundvalue2 = 8.0

    Set @Fundvalue3 = 6.9375

    Set @MulConstant1 = 0.000833333

    select (@revFundvalue1 * (1 + @MulConstant1 * @Fundvalue2)/(1+ @MulConstant1 * @Fundvalue3))

    Then I got the Result as '1.25910745127103'

    But If define @MulConstant1 as decimal(25,14) then I get the result value as '1.259107'

    But I want to generate value '1.25910745127103' even when I define @MulConstant1 as decimal.

    Please let me know why the computed value differs if the datatype is changed as decimal to float and vice versa

    Thanks

  • May 7, 2008 at 7:14 am

    #811745

    Some numbers cannot be stored with infinite precision in computers because computers in the end store only 0s and 1s. Some datatypes are more precise than others, but never 100% precise.

    The float is less precise than decimal that you use. It means that actual number you are multiplying by is not necessarily exaclty equal to what you write as 0.0083333

    Default float storage size is 8 bytes, while for decimals (25, 14) you use 17 bytes. The result you get when you declare @MulConstant1 as decimal(25, 14) is more accurate. It also matters how it is displayed and handled by the client you use to retrieve the result.

    Piotr

    ...and your only reply is slàinte mhath

  • May 7, 2008 at 7:40 am

    #811775

    Hi SSC Veteran

    Thank you very much for spontaneous response.

    I have computed the result for the operation I mentioned above in Excel and System calculator.

    They are generating the result('1.25910742125256949425') that is more close to the value that I generated with float('1.25910745127103').

    When I take 0.0083333 in a decimal datatype I am always deviated from the expected value that is generated in excel but the value came close to when I used the float type.

    So can u explain the reasons for that.

    Thanks

  • May 7, 2008 at 7:53 am

    #811796

    The reason is that Excel is using double datatype which is exactly same as float in SQL Server.

    Piotr

    ...and your only reply is slàinte mhath

Viewing 4 posts - 1 through 3 (of 3 total)

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