Arithmetic overflow error converting numeric to data type numeric in sql

Question

I have a query having code like this.

set @CurrentPatchDeployPercentage = convert(numeric(5,2), (isnull(@LastMonthDeployCount, 0)* 100.00 / isnull(nullif(@TotalMachines, 0), 1)))

But when I run I am getting below error, please help.

Arithmetic overflow error converting numeric to data type numeric

numeric(5,2) allows 5 total digits, 2 fractional meaning 5 - 2 = 3 total digits on the left. A result of your calculation exceeds 999.99 and the error is raised. Increase the size of the numeric() — Alex K.
– Alex K., Commented Feb 7, 2017 at 12:14
Can you try increase your numeric(5,2) to numeric(x,y) where x,y are relatively higher than what you specified — PowerStar
– PowerStar, Commented Feb 7, 2017 at 12:15
Possible duplicate of Why SQL Server throws Arithmetic overflow error converting int to data type numeric? — Tanner
– Tanner, Commented Feb 7, 2017 at 12:19
Hi guys I increased the values to numeric(6,3) but still the same error — vissubabu
– vissubabu, Commented Feb 7, 2017 at 13:05

SqlZim · Accepted Answer · 2017-02-07 13:19:35Z

1

Are you sure that is how you want to get your percentage? Should you move your *100.00 after the division like this?

rextester: http://rextester.com/NNOEH44668

declare @LastMonthDeployCount numeric(7,2) = 10.00;
declare @TotalMachines numeric (7,2) = 100.00;

declare @CurrentPatchDeployPercentage numeric(9,2);
    set @CurrentPatchDeployPercentage = 
      convert(numeric(5,2), 
        ( isnull(@LastMonthDeployCount, 0)
          / 
          isnull(nullif(@TotalMachines, 0), 1.0)
          ) * 100.00
        );

select @CurrentPatchDeployPercentage;

Also, make sure your @CurrentPatchDeployPercentage data type can support the highest number of @LastMonthDeployCount *100.00 for when @TotalMachines = 0 and is changed to null and then changed to 1.

Decimal/Numeric Data Type - MSDN

Precision  Storage bytes
---------  --------------
1 - 9      5
10-19      9
20-28      13
29-38      17

edited Feb 7, 2017 at 13:19

answered Feb 7, 2017 at 13:13

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Arithmetic overflow error converting numeric to data type numeric in sql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related