## SQL Server : 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

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))`

.

**SQL Server returns "Arithmetic overflow error converting expression ,** Arithmetic overflow error converting expression to data type int. Any idea on what is the cause of it? I'm just following the instructions in this With SQL Server, let’s insert some data into a table using a stored procedure for example. Facing this SQL Server error message can be disturbing “Arithmetic overflow error converting expression to data type int”. Even if SQL conversion errors are very classical ones, finding the solution and best practices to avoid them is very useful.

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.

**SQL Server Arithmetic overflow error converting expression to data ,** To avoid the SQL Server error : "Arithmetic overflow error converting expression to data type int" ? Convert the field to an INTEGER or a BIGINT In general, converting a date to a numeric or string, to perform date operations on it, is highly inefficient. (The conversions are relatively intensive, as are string manipulations.) It is much better to stick to just date functions. The example you give is (I believe) to strip away the time part of the DateTime, the following does that

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`

.

**Arithmetic overflow error converting expression to datatype int.,** expression to datatype int. DMS-E-GENERAL, a general exception error has occurred during operation attach database General SQL server Internally SQL Server is summing the values (to divide by the count later) and storing them in the columns data type - in this case an int - which isn't large enough to hold the sum - if you cast the value as a bigint first it will sum the values also storing those values in a bigint - which is probably large enough, then the average calculation can proceed.

On my side, this error came from the data type "INT' in the Null values column. The error is resolved by just changing the data a type to varchar.

**SUM, AVG, and arithmetic overflow,** Transact-SQL Arithmetic overflow error converting expression to data type int. and you had to track down the error message, what would you do? I teach SQL Server training classes, or if you haven't got time for the pain, Thanks for contributing an answer to Stack Overflow! Please be sure to answer the question.Provide details and share your research! But avoid …. Asking for help, clarification, or responding to other answers.

Very simple:

Use COUNT_BIG(*) AS NumStreams

**Arithmetic overflow error converting numeric to data type numeric in ,** Arithmetic overflow error converting numeric to data type numeric in SQL Server. This error comes when you try to store an out-of-range floating Microsoft distributes Microsoft SQL Server 2008 fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release.

**SQL SERVER,** Arithmetic overflow error converting expression to data type int. If you have not seen already, there was a series of blogs which I have written when I try to run my view but not sure what am I doing wrong with my calculation. I have researched but could not solve it so far. Here is the line of code that is causing the error: Your precision and scale arguments to NUMERIC are very small. Have you tried increasing those? Your numeric value can only handle numbers up to 9.99.

**Solved: Arithmetic overflow error converting expression to,** Arithmetic overflow error converting expression to data type int." Any suggestion please? select COUNT(*), SUM(SS.ItemSize)/1024 'size in MB' from saveset SS. I am using SQL Server 2008 R2 and I have an INT column where the data inserted never surpasses the max INT, but I have a query which uses the SUM function which when executed surpasses the max INT

**Arithmetic overflow error converting expression to data type int.SQL ,** SQL Server Arithmetic overflow error converting expression to data type int. CKWTech LLC Duration: 2:13
Posted: Oct 18, 2017 Arithmetic overflow error converting expression to data type int. This confirms that the column value of execution_count in the DMV – sys.dm_exec_query_stats has a value which his higher than INT Datatype.