none
Simple Round call results in Arithmetic overflow RRS feed

  • Question

  • Hi,

     

    I'm a bit dazzled why this statement won't work in T-SQL : Select Round(9.990000, 0)

    The error I get is : Arithmetic overflow error converting expression to data type numeric.

    The result should be : 10

    I've tried this in SQL 2005,2008 and 2008 R2 (Error message if from R2), didn't find anything in BOL either

    Select ROUND(19.99, 0) works just fine

     

    Is this a bug or not? We develop accounting software and for us this is a dangerous bug.

     

    Thursday, July 29, 2010 1:55 PM

Answers

All replies

  • Thats wierd.

    I tried

    8.990000 ,

    7.990000 and everything works. except 9.990000

     

    Thursday, July 29, 2010 2:14 PM
  • It is not a bug, it is expected because of the first datatype. e.g NUMERIC (7,6) It overflows because it is allowing only 1 digit before the decimal.

    Select Round(9.990000, 0)

    DECLARE @n2 NUMERIC (8,6)
    SET @n2 = 9.990000
    SELECT Round(@n2, 0)

    Therefore you must use appropriate types to hold the data and calculations.


    Jon
    Thursday, July 29, 2010 2:19 PM
  • I can't be certain here, never having come across it before but I suspect that the number must have the same length in the "integer" part when you use ROUND like that.

    E.G. This works, giving "10" because the type is declared for the number.

    DECLARE @TEST AS FLOAT
    SET @TEST = 9.990000
    SELECT @TEST,Round(@TEST, 0)
    

    Whereas all of these fail

    SELECT Round(9.990000, 0)
    
    SELECT Round(99.990000, 0)
    
    SELECT Round(999.990000, 0)
    
    Do I make any sense here? :-)
    Thursday, July 29, 2010 2:21 PM
  • This is because 9.990000 is treated as NUMERIC(7, 6) and after the rounding becomes NUMERIC(8, 6), so it overflows. To fix it you have to cast explicitly the argument to correct precision and scale:

    SELECT ROUND(CAST(9.990000 AS DECIMAL(8, 6)), 0);

    Looking at the ROUND documentation in BOL this may be considered a bug. According to this:

    http://msdn.microsoft.com/en-us/library/ms175003.aspx

    The return expression when the argument is NUMERIC(p, s) is DECIMAL(38, s), so it should have resulted in no error.


    Plamen Ratchev
    Thursday, July 29, 2010 2:25 PM
    Moderator
  • I can follow you in this, it's acward but logical.

    The correct call would be : Select Round(CAST(9.990000 as float), 0)

     

    Thank you all for your answers ...

     

    Regards,

    Sven Peeters

    BELGIUM

    Thursday, July 29, 2010 2:39 PM
  • I wouldnt develop accounting software while using the float datatype if i were you.

    http://msdn.microsoft.com/en-us/library/ms173773.aspx

    Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.


    Jon
    • Proposed as answer by Naomi NModerator Thursday, July 29, 2010 2:49 PM
    • Marked as answer by KJian_ Wednesday, August 4, 2010 9:50 AM
    Thursday, July 29, 2010 2:47 PM
  • you can try this SELECT Round(1*99.99,0) . it will work in Sql 2008
    • Proposed as answer by abychk Tuesday, September 11, 2012 2:53 PM
    Tuesday, September 11, 2012 2:52 PM
  • I tried this it worked well

    Convert(decimal,99.5) => 100

    Tuesday, May 27, 2014 6:57 AM
  • Take a look at this article that may explain the problem

    How SQL Server Determines Type of the Constant


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, May 27, 2014 2:03 PM
    Moderator