Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Arithmetic Overflow SQL

Posted on 2014-09-14
1
182 Views
Last Modified: 2014-09-14
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
0
Comment
Question by:SweetingA
1 Comment
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 40322239
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

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question