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

Commented:
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
``````
If it is still not enough ALTER to a different type, i.e. DECIMAL(6,2)
0

Experts Exchange Solution brought to you by