Ошибка arithmetic overflow error converting expression to data type int

I’m getting this error

msg 8115, level 16, state 2, line 18
Arithmetic overflow error converting expression to data type int.

with this SQL query

DECLARE @year VARCHAR(4);                       
DECLARE @month VARCHAR(2);                      

-- START OF CONFIGURATION SECTION                       
-- THIS IS THE ONLY SECTION THAT SHOULD BE MODIFIED                     
-- SET THE YEAR AND MONTH PARAMETERS                        

SET @year = '2013';                     
SET @month = '3';  -- 1 = January.... 12 = Decemeber.                       

-- END OF CONFIGURATION SECTION                     

DECLARE @startDate DATE                     
DECLARE @endDate DATE                       
SET @startDate = @year + '-' + @month + '-01 00:00:00';                     
SET @endDate = DATEADD(MONTH, 1, @startDate);                       

SELECT                          
    DATEPART(YEAR, dateTimeStamp) AS [Year]                         
    , DATEPART(MONTH, dateTimeStamp) AS [Month]                         
    , COUNT(*) AS NumStreams                        
    , [platform] AS [Platform]                      
    , deliverableName AS [Deliverable Name]                     
    , SUM(billableDuration) AS NumSecondsDelivered                      
FROM                            
    DeliveryTransactions                        
WHERE                           
    dateTimeStamp >= @startDate                     
AND dateTimeStamp < @endDate                        
GROUP BY                            
    DATEPART(YEAR, dateTimeStamp)                       
    , DATEPART(MONTH, dateTimeStamp)                        
    , [platform]                        
    , deliverableName                       
ORDER BY                            
    [platform]                      
    , DATEPART(YEAR, dateTimeStamp)                         
    , DATEPART(MONTH, dateTimeStamp)                        
    , deliverableName   

I’m getting this error

msg 8115, level 16, state 2, line 18
Arithmetic overflow error converting expression to data type int.

with this SQL query

DECLARE @year VARCHAR(4);                       
DECLARE @month VARCHAR(2);                      

-- START OF CONFIGURATION SECTION                       
-- THIS IS THE ONLY SECTION THAT SHOULD BE MODIFIED                     
-- SET THE YEAR AND MONTH PARAMETERS                        

SET @year = '2013';                     
SET @month = '3';  -- 1 = January.... 12 = Decemeber.                       

-- END OF CONFIGURATION SECTION                     

DECLARE @startDate DATE                     
DECLARE @endDate DATE                       
SET @startDate = @year + '-' + @month + '-01 00:00:00';                     
SET @endDate = DATEADD(MONTH, 1, @startDate);                       

SELECT                          
    DATEPART(YEAR, dateTimeStamp) AS [Year]                         
    , DATEPART(MONTH, dateTimeStamp) AS [Month]                         
    , COUNT(*) AS NumStreams                        
    , [platform] AS [Platform]                      
    , deliverableName AS [Deliverable Name]                     
    , SUM(billableDuration) AS NumSecondsDelivered                      
FROM                            
    DeliveryTransactions                        
WHERE                           
    dateTimeStamp >= @startDate                     
AND dateTimeStamp < @endDate                        
GROUP BY                            
    DATEPART(YEAR, dateTimeStamp)                       
    , DATEPART(MONTH, dateTimeStamp)                        
    , [platform]                        
    , deliverableName                       
ORDER BY                            
    [platform]                      
    , DATEPART(YEAR, dateTimeStamp)                         
    , DATEPART(MONTH, dateTimeStamp)                        
    , deliverableName   

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.

Arithmetic overflow error converting expression to data type int. is among a class of error messages that SQL Server shows when you’re trying to store a value that exceeds the maximum size of the target data type.

This article will use code examples that show you how this error can occur in SQL Server, and we’ll explain how you can fix it. In addition to that, you will learn in this article will show you the best way to work with data types in SQL Server without getting this error message. With that said, open your SQL Server, and let’s correct your SQL code.

Why Does Your SQL Lead to an Arithmetic Overflow Error?

Your SQL leads to an arithmetic overflow error because:

• Your value is too large for the INT data type
• Your BIGINT value cannot fit in nvarchar
• You exceeded the storage capacity of BIGINT
• The VARCHAR data type cannot store your conversion
• Your precision is smaller for your calculation

– Your Value Is Too Large for the Int Data Type

When your value is too large for the INT data type, that’s when you’ll see the “arithmetic overflow error converting expression to data type int. sum” error in SQL Server. This happens because the maximum value for the INT is 2,147,483,647, so an error will occur if your value exceeds this number.

For example, the following code will lead to an error because the sum of both numbers exceeds the limit of the INT data type. The same approach will lead to the “arithmetic overflow error converting expression to data type int C#” error.

– Your Bigint Value Cannot Fit In Nvarchar

The “arithmetic overflow error converting expression to data type nvarchar” occurs if you’re trying to store a BIGINT value that cannot fit in NVARCHAR. For example, in the next code block, the value of “@bigint” is “9223372036854775807”, and we set the size of NVARCHAR as 10 characters.

From this, “@bigint” will not fit in this NVARCHAR because its length (19) is more than the declared NVARCHAR (10). As a result, you’ll get an error when you run this code in your SQL Server.

– You Exceeded the Storage Capacity of Bigint

The “arithmetic overflow error converting expression to data type bigint.” occurs when your code tries to exceed the storage capacity of BIGINT. By default, the max BIGINT value is “9223372036854775807”, and a little increment of this value will lead to an arithmetic overflow.

For example, the following code declares BIGINT with its maximum value, and “@x” increments it by one. This will lead to an error because incrementing this BIGINT value will exceed what BIGINT can contain.

– The Varchar Data Type Cannot Store Your Conversion

The “arithmetic overflow error converting expression to data type VARCHAR” occurs when a converted decimal value is longer than your VARCHAR data type. For example, “@DecVal” in the next code block, is a decimal value with five precision numbers and a scale of three.

Then we’re trying to convert it to a VARCHAR with a size of five, but “@DecVal” will not fit. That’s because when you count its value, “99.999” (including the decimal point), you’ll get six, and this is more than the size of the declared VARCHAR, five.

– Your Precision Is Smaller for Your Calculation

You’ll get the “arithmetic overflow error converting expression to data type numeric” error if a precision number is smaller for your calculation. For example, the “SELECT” operation in the next code block is trying to cast 475 to a decimal data type with a precision of three and a scale of two.

However, 475 has three digits before the decimal point, and therefore it cannot be stored in the “DECIMAL(3, 2)” data type without loss of information. As a result, this “SELECT” operation will result in an arithmetic overflow error in SQL Server.

How To Convert Expressions Without an Overflow Error?

To convert expressions without an overflow error, you have to:

• Use BIGINT in your arithmetic calculations
• Increase the size of the NVARCHAR data type
• Replace BIGINT with DECIMAL
• Use the appropriate size for VARCHAR
• Adjust the precision in your code

– Use Bigint in Your Arithmetic Calculations

Using a BIGINT in your arithmetic calculations will solve the “msg 8115, level 16, state 2, line 1 arithmetic overflow error converting expression to data type INT” error. This will also solve the error in our first example that resulted in an overflow error in SQL Server.

Again, here is the code from our first example:

Now, the next code block contains the fix, and the all-important line is “SELECT SUM(cast(value as bigint)) as Total FROM @table”.

This line ensures that the addition of 2147483647 in two places will fit in BIGINT without causing an overflow.

– Increase the Size of the Nvarchar Data Type

Increasing the size of the NVARCHAR data type in your code will ensure that you can store the needed data. As a reference, we showed you the following code that led to an arithmetic overflow error because the length of BIGINT did not fit in the VARCHAR:

Now, the following is the correct way, and the key takeaway of this solution is the increase of NVARCHAR from NVARCHAR(10) to NVARCHAR(20).

– Replace Bigint With Decimal

If you’re getting arithmetic overflow while using the BIGINT data type, replace it with DECIMAL. As a reminder, the following code will lead to an error:

The following is the result of the code above:

– Use the Appropriate Size for Varchar

Use the appropriate size for VARCHAR if you want to store a value in it and you’re getting an overflow error. Ensure that your increment will contain all the figures in the number that you’re trying to store. Earlier, we showed you the following:

The correction is to change the value of VARCHAR in the last line to a large one that’ll accommodate “99.999”, including the decimal point.

Here, six will suffice, but use a number that will fit your use case.

– Adjust the Precision in Your Code

If you’re casting a number to decimal, always use a large precision number that will prevent the arithmetic overflow error.

For example, the following code leads to an arithmetic overflow error because the precision is small for 475:

SELECT CAST(475 AS DECIMAL(3, 2));

To correct the error in the code, we change “DECIMAL(3, 2)” to “DECIMAL(5,2)”.

The latter will prevent the overflow, and you’ll get “475.00” as a result.

SELECT CAST(475 AS DECIMAL(5, 2));

Conclusion

In this article, we explained how your SQL code can lead to an arithmetic overflow and how you can solve it. From our discussion, remember the following, and you’ll keep the error away from SQL Server:

• An arithmetic overflow error occurs when you exceed the storage capacity of the target data type.
• The best way to prevent the arithmetic overflow in SQL Server is to ensure that your value can fit in a data type.
• The BIGINT data type is how to solve arithmetic overflow error, converting expression to data type int.
• When you’re casting an integer like 475, always use a good precision that will prevent an overflow.

Now, you should feel elated that you’ve solved the arithmetic overflow in your SQL server. Our article is here for you when you see the mentioned error in the future, so bookmark it and don’t forget to share it.

References

• https://learn.microsoft.com/en-us/cpp/cpp/integer-limits?view=msvc-170