SQL Server : Arithmetic overflow error converting expression to data type int

Question

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

please can you point out which number is too large for data type? thanks — user2270544, Apr 11 '13 at 14:00
For dates as string, you should always use the **ISO-8601** format - `YYYYMMDD` - only this format is guaranteed to work for **any** language and/or regional settings — marc_s, Apr 11 '13 at 14:06
this works SET @month = '2'; -- 1 = January.... 12 = Decemeber. — user2270544, Apr 11 '13 at 14:17
@marc_s, can we say the same about YYYY-MM-DD? Because I am using this format in all of my projects. Thanks in advance. — anar khalilov, Nov 28 '13 at 07:46
@Anar: if you use `YYYY-MM-DD` for a `DATE` datatype - you're safe. For `DATETIME` however, it is **NOT SAFE** -- don't use it that way! Use `YYYYMMDD` only (**no** dashes!). To demonstrate, run this little snippet of code in SQL Server Mgmt Studio: `SET LANGUAGE german; SELECT CAST('2013-11-25' AS DATETIME)` - and you'll get a German error message telling you it cannot convert the string to a `DATETIME`. `SELECT CAST('20131125' AS DATETIME)` (**without** dashes!) works just fine. — marc_s, Nov 28 '13 at 07:48
Wow, this is really helpful, thanks for that. I am following your input on most SQL related questons, so keep it coming. — anar khalilov, Nov 28 '13 at 07:58

score 128 · Answer 1 · edited Mar 19 '14 at 10:53

128

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

edited Mar 19 '14 at 10:53

davmos

9,324
4
40
43

answered Apr 11 '13 at 13:59

Jeff Johnston

2,076
1
14
27

1

This `CAST(` works perfectly with me, thanks – Mohamad Osama Apr 19 '22 at 15:17

score 29 · Answer 2 · edited Dec 23 '19 at 19:03

29

Very simple:

Use COUNT_BIG(*) AS NumStreams

edited Dec 23 '19 at 19:03

סטנלי גרונן

2,917
23
46
68

answered Dec 23 '19 at 18:41

John G

319
4
8

score 21 · Answer 3 · answered Jan 18 '16 at 05:31

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.

score 1 · Answer 4 · answered Apr 11 '13 at 14:43

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.

score 0 · Answer 5 · answered Aug 28 '18 at 19:45

0

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.

answered Aug 28 '18 at 19:45

John Tecle

1

Can you please show some code, thought that might be helpful. – Adeel Shekhani Nov 05 '21 at 13:19

score -4 · Answer 6 · edited Dec 27 '16 at 05:36

-4

declare @d real
set @d=1.0;
select @d*40000*(192+2)*20000+150000

edited Dec 27 '16 at 05:36

Andy

49,085
60
166
233

answered Dec 27 '16 at 05:34

user7344470

1

12

could you please add some explanation to your answer? – slfan Dec 27 '16 at 05:57

SQL Server : Arithmetic overflow error converting expression to data type int

6 Answers6

Linked