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
You need to increase the width of the variable to store this number e.g. making @sample NUMERIC (6,2) will solve this error.
1. "Arithmetic overflow error converting IDENTITY to data type int" error means the value of IDENTITY is overflowing range of data type of that particular column. 2. Check the current value of Identity.
The solution to avoid this arithmetic overflow error is to change the data type from INT to BIGINT or DECIMAL(11,0) for example.
The error "Arithmetic overflow error converting IDENTITY to data type int" comes when IDENTITY value is inserted into a column of data type int, but the value is out-of-range.
Is the problem with SUM(billableDuration)
? To find out, try commenting out that line and see if it works.
It could be that the sum is exceeding the maximum int
. If so, try replacing it with SUM(CAST(billableDuration AS BIGINT))
.
Very simple:
Use COUNT_BIG(*) AS NumStreams
Change SUM(billableDuration) AS NumSecondsDelivered
to
sum(cast(billableDuration as bigint))
or
sum(cast(billableDuration as numeric(12, 0)))
according to your need.
The resultant type of of Sum expression is the same as the data type used. It throws error at time of overflow. So casting the column to larger capacity data type and then using Sum operation works fine.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With