CODEWITHSUNDEEP
sqlsql-servert-sqldatediffdateadd
Martin
Martin

Reputation: 43

Arithmetic overflow error: Msg 8115, Level 16, State 2

I've got an issue with a stored procedure causing an:

Arithmetic overflow error converting expression to data type int.

The are two tables, the first pulls in data from an ODBC connection. The data contains activities and the times they start. There is a stored procedure which runs daily that converts these start times into intervals.This works the vast majority of the time, but there have been about a dozen days in the last year that this has failed due to the arithmetic overflow error. I've attempted to change data types from INT to Numeric, but this hasn't solved the problem. Any help/thoughts/ideas are greatly appreciated!

The stored procedure:

insert into [DB].[dbo].[Final_Table]
([resourceName]
      ,[eventdatetime]
      ,[EV2]
      ,[IntervalTime]
      ,[event])

(
SELECT DS1.[resourceName]
      ,DS2.[eventdatetime]
      ,DS1.[eventdatetime] as EV2
      ,CONVERT(varchar(8), DATEADD(ms, DATEDIFF(SECOND, DS2.[eventdatetime], DS1.[eventdatetime]) * 1000, 0), 114) AS [IntervalTime]
      ,DS2.[event]
FROM [DB].[dbo].[Staging_Table] DS1

INNER JOIN [DB].[dbo].[Staging_Table] DS2
    ON DS1.[resourceName] = DS2.[resourceName]
    AND DS1.[ID] = DS2.[ID] + 1)

The staging table:

CREATE TABLE [dbo].[Staging_Table](
    [ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [resourceName] [varchar](50) NULL,
    [eventdatetime] [datetime] NULL,
    [event] [varchar](9) NULL,
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Data in staging table:

enter image description here

The final table:

CREATE TABLE [dbo].[Final_Table](
    [resourceName] [varchar](50) NULL,
    [eventdatetime] [datetime] NULL,
    [EV2] [datetime] NULL,
    [IntervalTime] [varchar](8) NULL,
    [event] [varchar](9) NULL
) ON [PRIMARY]
GO

Final Table data:

enter image description here

Upvotes: 1

Views: 5551

Answers (1)

gotqn
gotqn

Reputation: 43636

The DATEDIFF functions return an INT. Then you are multiplying it with 1000 to get the results from seconds in milliseconds.

But, sometimes this may result in overflow. For example:

DECLARE @A DATETIME2 = '2000-01-01'
       ,@B DATETIME2 = '2001-01-01'


SELECT DATEDIFF(SECOND, @A, @B) * 1000

Msg 8115, Level 16, State 2, Line 5 Arithmetic overflow error converting expression to data type int.

So, just cast to BIGINT first:

DECLARE @A DATETIME2 = '2000-01-01'
       ,@B DATETIME2 = '2001-01-01'


SELECT CAST(DATEDIFF(SECOND, @A, @B) AS BIGINT) * 1000

Actually, the above will not solve your issue, because if you check the DATEADD function:

The number argument cannot exceed the range of int. In the following statements, the argument for number exceeds the range of int by 1. These statements both return the following error message: "Msg 8115, Level 16, State 2, Line 1. Arithmetic overflow error converting expression to data type int."

That's mean that even you pass BIGINT value to it (after the cast), you will continue to get this error:

DECLARE @C BIGINT = 31622400000    
SELECT DATEADD(MILLISECOND,  @C  , 0)

You will need to handle this situations in a different way. For example, set a MAX value if your differences exceed the INT.

DECLARE @A DATETIME2 = '2000-01-01'
       ,@B DATETIME2 = '2001-01-01'

SELECT TRY_CAST(CAST(DATEDIFF(SECOND, @A, @B) AS BIGINT) * 1000 AS INT)

Upvotes: 3

Related Questions