Sql count arithmetic overflow error converting expression to data type int - Исправление ошибок и поиск оптимальных решений проблем

If you’re receiving error Msg 8115, Level 16, Arithmetic overflow error converting expression to data type int in SQL Server, it could be that you’re performing a calculation that results in an out of range value.

This can happen when you use a function such as SUM() on a column, and the calculation results in a value that’s outside the range of the column’s type.

Example of the Error

Here’s an example of code that produces the error:

SELECT SUM(bank_balance) 
FROM accounts;

Result:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

In this case I used the SUM() function to get the sum of the bank_balance column, which has a data type of int.

The error occurred because the result of the calculation is outside the range of the int data type.

Here’s all the data in my table:

SELECT bank_balance 
FROM accounts;

Result:

+----------------+
| bank_balance   |
|----------------|
| 1300000000     |
| 1200000000     |
| 800500000      |
+----------------+

Those are some big bank balances… and adding the three of them results in a larger number than an int can handle (the int range is -2,147,483,648 to 2,147,483,647).

The Solution

We can deal with this error by converting the int column to a bigint when we run the query:

SELECT SUM(CAST(bank_balance AS bigint)) 
FROM Accounts;

Result:

3300500000

This time it worked.

You could also change the data type of the actual column for a more permanent solution.

In case you’re wondering, the bigint range is -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

Same Error in Different Scenarios

The same error (Msg 8115) can also occur (with a slightly different error message) when you try to explicitly convert between data types and the original value is outside the range of the new type. See Fix “Arithmetic overflow error converting int to data type numeric” in SQL Server to fix this.

The same error (Msg 8115) can also occur (with a slightly different error message) when you try to insert data into a table when its IDENTITY column has reached its data type’s limit. See Fix: “Arithmetic overflow error converting IDENTITY to data type…” in SQL Server for how to fix this.

Источник

Remove From My Forums

Question
Hello, everyone,

I encountered the error :»Arithmetic overflow error converting express to data type int» when updating a table as follows:

UPDATE
t1

SET
t1.Count1 = t2.Count2

FROM
Table1 t1

JOIN
Table2 t2

ON
t2.Id =
t1.Id

t1.Count1’s data type: INT

t2.Count2’s data type: BIGINT

I understand that by changing t1.Count1’s data type to BIGINT would solve the problem.

My question is — Is there a way to fix this problem without changing the t1.Count1 data type to BIGINT?

Your help is much appreciate it!
- Edited by
  
  Sunday, February 2, 2014 11:14 PM

Answers

Try the below:

Drop table t1,t2
CREATE TABLE t1 (Id INT, Count1 INT)
CREATE TABLE t2 (Id INT, Count2 BIGINT)
INSERT INTO t1 (Id) VALUES (1);
INSERT INTO t2 (Id, Count2) VALUES (1, 2147483648);
UPDATE t1
SET t1.Count1 = t2.Count2
FROM t1
JOIN t2
ON t2.Id = t1.Id
--where t2.Count2 <2147483648 --max value for int
where t2.Count2 between -2147483648 and 2147483647

Select * from t1

Edited by
SQLZealots
Tuesday, February 4, 2014 6:03 AM
Marked as answer by
pituachMVP
Saturday, August 2, 2014 5:25 PM

Источник

With SQL Server, how to avoid this error : “Arithmetic overflow error converting expression to data type int”. Let’s insert some data into a table using a stored procedure for example. Facing this SQL Server error message can be disturbing. Even if SQL conversion errors are classical ones, finding the solution and apply the industry best practices to avoid them is very useful.

In this case, the stored procedure executing the query generates some dynamic T-SQL code. Then it executes the code and insert data from various tables into one single target table. It’s a typical SQL Server int arithmetic overflow conversion error.

How to avoid the SQL Server Arithmetic overflow error?

While running the same piece of T-SQL code independently, that is outside the stored procedure, directly into the SQL Server Management Studio windows, no error is faced. But when integrating the same code into the stored procedure, this error is thrown:

Msg 50000, Level 16, State 2, Procedure MyStoredProcedure, Line 123
Arithmetic overflow error converting expression to data type int.

After analysis, the conversion error message didn’t come from the SELECT clause. The SQL INSERT clause from the same T-SQL stored procedure is throwing the error. The root cause is the the insertion of a big integer value into the table. For integer Data Type in SQL Server, the number must be between -2^31 (-2 to the power of 31) and 2^31 (2 to the power of 31).

Namely the range for the INTEGER type is exactly between -2,147,483,648 and 2,147,483,648.

Use another data type to avoid the conversion error

The solution to avoid Arithmetic overflow error converting expression is to use a bigger data type. The solution to avoid this arithmetic overflow error is to change the data type from INT to BIGINT or DECIMAL(11,0) for example.

Please note that in this case the conversion error is because the integer number is too big. It can also be an insertion of a text value into an integer field. Check out the int, bigint, smallintand tinyint official documentation for SQL Server.

Example of the Error

The Solution

Same Error in Different Scenarios

Question

Answers

How to avoid the SQL Server Arithmetic overflow error?

Use another data type to avoid the conversion error

Other classical conversion questions