Solved

Arithmetic Overflow SQL

Posted on 2014-09-14
1
177 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Service Statictic 11 30
all records from previous month 6 45
xpath sql query 2008 8 43
How to pass a parameter to limit the colums returned in Stored Procedure SQL 2016 2 30
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now