SQL Server tutorial with workaround to avoid the “Arithmetic overflow error converting expression to data type int” error.
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.
The SQL Server Arithmetic overflow conversion error
In our use 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 arithmetic overflow conversion 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 a compatible data type to avoid the 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 SQL Server documentation.
Other classical conversion questions
This article shows how to simply avoid an Arithmetic overflow error converting expression to int in SQL Server. It happens when inserting a bigger number in an integer data type. Check out more classical SQL Server errors like the insert of a duplicate key.
An arithmetic overflow error is an error faced when converting from one SQL Server data type to another one. For example from a numeric figure to an integer because the integer type can handle smaller figures compared to the numeric type.
The biggest number for the SQL Server integer data type is from -2 to the power of 31 till 2 to the power of 31. It means from -2^31 to 2^31 which is exactly this range: from -2,147,483,648 to 2,147,483,648. To avoid conversion error, always consider the maximum value for the data and size the database and the data types accordingly.
A bigint is an integer, i.e. a number without decimals, and in SQL Server it can go from -2^63 that is exactly -9,223,372,036,854,775,808 to 2^63-1 which is 9,223,372,036,854,775,807 and is using 8 Bytes in storage. While an INTEGER is from -2^31 which equals -2,147,483,648 to 2^31-1 which is exactly 2,147,483,647. An INTEGER data is stored on 4 Bytes.
One good practice to avoid conversion errors during is to put in place controls for the inputs. And moreover make sure to control the values inserted in the database. For example, users can only enter valid dates in a date field.
Check out how to manage time difference in hours minutes and also seconds with T-SQL.
