Arithmetic Overflow SQL

I have a stored procedure which used to work perfectly but has started to error with
"Msg 8115, Level 16, State 8, Procedure stp_UpdateTempStops, Line 9
Arithmetic overflow error converting int to data type numeric"

INSERT INTO temptbl_Stops (StartDate, StopDate, StopTime, DownTimeCode)
SELECT     TOP (100) PERCENT StartDate, StopDate, Avg(cast(DATEDIFF(s, StartDate, StopDate)/60 AS DECIMAL(4,2))) AS StopTime, DowntimeCode
FROM         dbo.tbl_MachineData
GROUP BY StartDate, StopDate, DowntimeCode
HAVING       Avg(cast(DATEDIFF(s, StartDate, StopDate)/60 AS decimal(4,2))) IS NOT NULL and DowntimeCode IS NULL and StopDate IS NOT NULL
ORDER BY StartDate

All help most welocme.....I am using SQL2012 Express
SweetingAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
The difference between these two dates (StartDate, StopDate) when divided by 60 seconds (i.e. converted to minutes) is greater than 99.99 minutes. You need to alter table temptbl_Stops to allow more ranges for StopTime column. E.g. if you wish to have the StopTime to be in 999.99 range use this syntax:
ALTER TABLE temptbl_Stops ALTER COLUMN StopTime DECIMAL(5,2) NULL
GO
INSERT INTO temptbl_Stops (StartDate, StopDate, StopTime, DownTimeCode)
SELECT     TOP (100) PERCENT StartDate, StopDate, Avg(cast(DATEDIFF(s, StartDate, StopDate)/60 AS DECIMAL(5,2))) AS StopTime, DowntimeCode
FROM         dbo.tbl_MachineData
GROUP BY StartDate, StopDate, DowntimeCode
HAVING       Avg(cast(DATEDIFF(s, StartDate, StopDate)/60 AS decimal(5,2))) IS NOT NULL and DowntimeCode IS NULL and StopDate IS NOT NULL
ORDER BY StartDate

Open in new window

If it is still not enough ALTER to a different type, i.e. DECIMAL(6,2)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.