none
Arithmetic overflow error converting numeric to data type numeric. RRS feed

  • Question

  • Hi 
    I have a below scenario,

    DECLARE @zero_num numeric(5,4)
    SET @zero_num = 0.00

    SELECT colA,colB,@Zero_num as ratio ,colC into #tmp
    FROM table
    UPDATE #tmp
    SET ratio = colA/ colb WHERE colC 0

    Select * from #tmp

    when we divide colA and ColB and assign to ratio columm iam getting overflow error because the output of the division was 248.9787565454656 .

    So can you please suggest a work around.Iam using sql server 2008 R2.Thanks in advance

    Friday, January 3, 2014 10:57 PM

Answers

  • Hi Manish,

    If you do not require the exact fractional output then you can use float data type. Else if you need the accurate fraction value then you can use the Decimal or Numeric data type with proper precision.


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    • Marked as answer by manish2kk Monday, January 6, 2014 2:38 PM
    Saturday, January 4, 2014 12:59 AM
  • I want to display it in front end but when run the above query iam getting over flow error..I want to display as 248.9

    DECLARE @IamSmall numeric(5,4)
    select @IamSmall = 9.9999 -- max value
    -- no error by above
    select @IamSmall = 10.0 -- overflow
    /*
    Msg 8115, Level 16, State 8, Line 3
    Arithmetic overflow error converting numeric to data type numeric.
    */
    solution: Increase the total length of the numeric datatype

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Saturday, January 4, 2014 2:06 AM
    Moderator
  • i think you are confused about numeric data type precision and scale.

    Precision
    The maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point.

    Scale
    The number of decimal digits that will be stored to the right of the decimal point. This number is substracted from p to determine the maximum number of digits to the left of the decimal point.

    in your case your precision is 5 and scale is 4, that means you can not have more than single digit on the right side of the decimal point.

    please refer below article

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


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page


    Saturday, January 4, 2014 5:03 AM

All replies

  • Change the data type of the target column? Or what is your question? You are not looking for a way to store 248 into a column which does not permit greater values than 9.9999, are you?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, January 3, 2014 11:26 PM
  • I want to display it in front end but when run the above query iam getting over flow error..I want to display as 248.9
    Friday, January 3, 2014 11:30 PM
  • Hi Manish,

    If you do not require the exact fractional output then you can use float data type. Else if you need the accurate fraction value then you can use the Decimal or Numeric data type with proper precision.


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    • Marked as answer by manish2kk Monday, January 6, 2014 2:38 PM
    Saturday, January 4, 2014 12:59 AM
  • I want to display it in front end but when run the above query iam getting over flow error..I want to display as 248.9

    DECLARE @IamSmall numeric(5,4)
    select @IamSmall = 9.9999 -- max value
    -- no error by above
    select @IamSmall = 10.0 -- overflow
    /*
    Msg 8115, Level 16, State 8, Line 3
    Arithmetic overflow error converting numeric to data type numeric.
    */
    solution: Increase the total length of the numeric datatype

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Saturday, January 4, 2014 2:06 AM
    Moderator
  • i think you are confused about numeric data type precision and scale.

    Precision
    The maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point.

    Scale
    The number of decimal digits that will be stored to the right of the decimal point. This number is substracted from p to determine the maximum number of digits to the left of the decimal point.

    in your case your precision is 5 and scale is 4, that means you can not have more than single digit on the right side of the decimal point.

    please refer below article

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


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page


    Saturday, January 4, 2014 5:03 AM