SQL Server Performance

Problem with casting to float in the query

Discussion in 'SQL Server 2008 General Developer Questions' started by Goldking, Mar 13, 2009.

  1. Goldking New Member

    There is a select query in the existing application which I have to work, which contains the formula that deals with currency value as,
    SELECT CAST((CAST(RecordCount as FLOAT(20) * CAST(Sum(Power(Amount,2)) as FLOAT(20)) - Power(Sum(Amount),2)) / CAST((RecordCount * (RecordCount- 1)) as FLOAT(20))
    Consider below are the values for this expression,
    SELECT CAST((57 * CAST(8056893945.093 as FLOAT(20)) - 207067699401.4860) / (57 * (57- 1)) as FLOAT(20))
    But in the above expression the CAST(8056893945.093 as FLOAT(20)) is giving the result as 8.056894E9 which is equivalent to 8056894000 right.
    Because of this truncation or rounding, the final value I am getting is 79002280 (If I calculate manually the exact result by zero decimal place is 79002273).
    Could any one please explain why the truncation or rounding off happens (My assumption here is value out of range) and how the rounding off is carried out in SQL Server in this type of case?
    Thanks In Advance[:)]
  2. Amarlai New Member

    Hi,
    Having a problem using an SQL query i don't know how to count the number of stored procedures inside a database using an sql query?
    Any help would be appreciated?
  3. FrankKalis Moderator

    [quote user="Amarlai"]
    Hi,
    Having a problem using an SQL query i don't know how to count the number of stored procedures inside a database using an sql query?
    Any help would be appreciated?
    [/quote]
    Please don't hijack other threads. Open a new thread if the problem still exists.
  4. patel_mayur New Member

    When you use FLOAT(20) for casting the precision would be 7 digit only thats why you see the truncation/rounding. Don't use any number any use only FLOAT which will take the default value as 53 and you will not see any truncation.
    For more information, please look into: http://msdn.microsoft.com/en-us/library/ms173773.aspx
  5. Goldking New Member

    Yes even I found the same reason. But this has been used in theexisting application, so before changing that I have clarified thiswith experts. Thanks for your help[:D]

Share This Page