SQL Server : Arithmetic overflow error converting expression to data type int
Solution 1
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))
.
Solution 2
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.
Solution 3
Very simple:
Use COUNT_BIG(*) AS NumStreams
Solution 4
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
Assuming that your quoted text is the exact text, one of these columns can't do the mathematical calculations that you want. Double click on the error and it will highlight the line that's causing the problems (if it's different than what's posted, it may not be up there); I tested your code with the variables and there was no problem, meaning that one of these columns (which we don't know more specific information about) is creating this error.
One of your expressions needs to be casted/converted to an int in order for this to go through, which is the meaning of Arithmetic overflow error converting expression to data type int
.
![Admin](/assets/logo_square_200-5d0d61d6853298bd2a4fe063103715b4daf2819fc21225efa21dfb93e61952ea.png)
Admin
Updated on December 24, 2020Comments
-
Admin over 3 years
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
-
slfan over 7 yearscould you please add some explanation to your answer?
-
Adeel Shekhani over 2 yearsCan you please show some code, thought that might be helpful.
-
Mohamad Osama about 2 yearsThis
CAST(
works perfectly with me, thanks