Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010
    Posts
    3

    Unanswered: Arithmetic overflow error converting float to data type numeric

    I'm having a bit of an issue. It seems simple, but I can't seem to get it fixed.

    I'm doing calculations. Grabbing total number of surveys, total number of negative surveys and total number of positive surveys. Then I'm trying to return a positive and negative percentage.

    declare @npos as int
    declare @nneg as int
    declare @tot as int
    Declare @retval as decimal

    set @retval = @nneg/@tot


    This is simplified obviously. It all works fine unless the total and negative (and presumably positive) are the same. so if i have 1 response and its negative, it sets @retval = 1/1. when i do this in a query window the response is 1.00000000000000000. Thats where I assume the problem is coming from.

    I've tried extending the decimal size, tried numeric, tried float, tried setting the int's to decimal/numeric, tried rounding, tried floor, and casting the numbers both before and after calculation. I cannot get anything to work correctly.

    What I need back.. if its 1.0000000000000 then i need 1.00. Then I will make it a percent in my gridview.

    Would appreciate the help. I'm pretty lost.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Part of the problem is that the calculation is done with all integers. This will introduce a number of rounding errors.
    Code:
    declare @npos as int
    declare @nneg as int
    declare @tot as int
    Declare @retval as decimal (10, 5)
    
    set @tot = 2
    set @nneg = 1
    
    set @retval = @nneg/@tot
    select @retval
    
                         
    -------------------- 
    0
    
    (1 row(s) affected)
    I believe you want to have .5 displayed. In that case, you should either declare the @tot, @nneg, and @npos variables to be decimal, as well. You should also apply scale and precision parameters to the definitions, in order to make sure you are getting the correct rounding.
    Code:
    declare @npos as decimal (5, 0)
    declare @nneg as decimal(5, 0)
    declare @tot as decimal(5, 0)
    Declare @retval as decimal (6, 5)
    
    set @tot = 2
    set @nneg = 1
    
    set @retval = @nneg/@tot
    select @retval

  3. #3
    Join Date
    Mar 2010
    Posts
    3
    Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •