SQL Throwing an Arithmetic Overflow Error

Question

I'm getting an "Arithmetic overflow error converting expression to data type int" error when running a simple aggregate function (see code below). As I throttle back the date range, I can see that the results is in fact exceeding the INT data type limit...I've tried casting the whole expression into a BIGINT w/o success. Anyone know how I can resolve this issue?

All 3 of these queries throw the same above mentioned error:

Select  (SUM(ACDTalkTimeInSec) + SUM(TotAcwTimeInSec) + SUM(HoldTimeInSec)) as AHT
From ColTelephony.dbo.vwACDSkillCombined acd
Where WorkDte between '1/2/2018' AND '10/5/2018'

Select  CAST(Cast(SUM(ACDTalkTimeInSec) AS BIGINT) + CAST(SUM(TotAcwTimeInSec) AS BIGINT) + CAST(SUM(HoldTimeInSec) AS BIGINT) AS BIGINT) as AHT
From ColTelephony.dbo.vwACDSkillCombined acd
Where WorkDte between '1/2/2018' AND '10/5/2018'

Select  Cast(((SUM(ACDTalkTimeInSec) + SUM(TotAcwTimeInSec) + SUM(HoldTimeInSec))) AS BIGINT) as AHT
From ColTelephony.dbo.vwACDSkillCombined acd
Where WorkDte between '1/2/2018' AND '10/5/2018'

Thank! Worked perfect! I hate when the answer is so simple like that lol. Please post as answer and I will mark it as such. Thanks again! — wlfente
– wlfente, Commented Oct 9, 2018 at 17:33

Michael Bruesch · Accepted Answer · 2018-10-09 17:42:22Z

2

SMor answered it in the comments, but here is the sql:

Select SUM(CAST(ACDTalkTimeInSec AS BIGINT)) + SUM(CAST(TotAcwTimeInSec AS BIGINT)) 
    + SUM(CAST(HoldTimeInSec AS BIGINT)) as AHT
From ColTelephony.dbo.vwACDSkillCombined acd
Where WorkDte between '1/2/2018' AND '10/5/2018'

If SMor posts their answer then accept that. I did not test the code so pardon any typos.

answered Oct 9, 2018 at 17:42

Michael Bruesch

6608 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeeva N Over a year ago

I was searching for the same issue. Thanks @Michael

Collectives™ on Stack Overflow

SQL Throwing an Arithmetic Overflow Error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related