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

  • Question

  • Declare @Number Numeric(5,2)=0.2500
    Declare @pagebreak INT =500
    Declare @result Numeric(5,2)

    Select @result=  @pagebreak/@Number

    I increased the @result datatype value , still i am getting error

    Msg 8115, Level 16, State 8, Line 6

    Arithmetic overflow error converting numeric to data type numeric.

    Tuesday, November 5, 2013 12:19 PM

Answers

All replies

  • Because SQL Server tries to convert the highest data type precedence (numeric)

    select 500/0.2500

    select cast(2000.000000 as decimal(5,2))-error


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, November 5, 2013 12:36 PM
    Answerer
  • Marimuthu,

    As per your declaration for result variable, oly 3 digits available for result before the decimal point while the answer for the calculation is 4 digits long (2000 s the answer)

    Hence modify the declaration for result:

    declare @result numeric(7,2) 
    -- means, 2 digits after decimal point(scale) 
    --and 7-2=5 digits before decimal point..

    This shud solve the problem..


    Thanks,
    JK

    <Please mark as 'answered' if this answers your query> <Please mark as 'helpful' if this was helpful to your query>

    Tuesday, November 5, 2013 12:38 PM
  • To workaround

    Declare @Number decimal(5,2)=0.2500
    Declare @pagebreak INT =500
    Declare @result decimal(18,3)
    Select @result=  @pagebreak/@Number 
    select @result


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, November 5, 2013 12:39 PM
    Answerer
  • Declare @Number Numeric(5,2)=0.2500
    Declare @pagebreak INT =500
    Declare @result Numeric(5,2)

    Select @result=  @pagebreak/@Number

    I increased the @result datatype value , still i am getting error

    Msg 8115, Level 16, State 8, Line 6

    Arithmetic overflow error converting numeric to data type numeric.

    The result of this calculation is 2000 but the @result variable is only numeric(5,2), allowing a max value of 999.99.  You need to increase to (at least) numeric(6,2).


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Tuesday, November 5, 2013 12:52 PM