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
Может довольно простой вопрос.
Но хотелось бы узнать механизм работы.
Почему
SELECT 2000000000 * 3
--Ошибка арифметического переполнения при преобразовании expression к типу данных int.
SELECT 3000000000 * 3
-- 9000000000Если создать вью
create view ts
as
SELECT 2000000000 * 3 AS A, 3000000000 * 3 AS B
, то в типах данных увидим такое
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
The issue is either with the average aggregation or the count(*).
If the sum of the values in column is greater than 2,147,483,647 you will need to cast the column as a bigint before averaging because SQL first sums all of the values in column then divides by the count.
If the count of the rows is more than 2,147,483,647, then you need to use count_big.
The code below includes both fixes so it should work.
SELECT
MIN(column) as min,
MAX(column) as max,
AVG(Cast(Column AS BIGINT)) as avg,
count_big(*) as count
FROM database.dbo.table;
You don’t need to cast min or max to bigint because these values exist in the table already without overflowing the int data type.
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.