Sql-server – The datediff function resulted in an overflow. But should not

datetime2sql serverstored-procedures

I have this stored procedure in SQL-Server 2014

USE [provbank]
GO
/****** Object:  StoredProcedure [dbo].[GetHBSeconds]    Script Date: 2018-10-05 08:40:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetHBSeconds]
AS
BEGIN

DECLARE @hbdatetime datetime
DECLARE @diffSec int

SET @hbdatetime = (SELECT HBDateTime from HBData WHERE ID=1)

SET @diffSec = datediff(second,@hbdatetime, convert(datetime,convert(char(19), getdate(),126)))

SELECT @diffSec AS DiffInSeconds , @hbdatetime AS 'LastHB', convert(datetime,convert(char(19), getdate(),126)) AS 'CurrentTime'


END

And every now and then I get this error:

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

What I have in the database is one row with a datetime column and an id.

I have a separate program that writes in this row the current timedate like '2018-10-05 09:58:30.000'. And then I have the SP to calculate between current time and the timestamp to get # of seconds in difference between now and the timestamp. I don't think having the diff in seconds would be too narrow/too precise.

Is there something I can change in my SP to avoid this error?

Because the difference in the table and getdate() on the same machine/server shouldn't be more then a few seconds apart if all works, which it is, when I get this error.

Best Answer

First: the expression convert(datetime,convert(char(19), getdate(),126)) is redundant (except for the GETDATE(), of course). So, just replace that with GETDATE().

We need something to test with. The error message is clear, you clearly have a value for @hbdatetime which is too old, so that the number of seconds compared to GETDATE() overflows an int.

You can calculate the approx earliest allowable date compared to GETDATE() using:

SELECT DATEADD(SECOND, -2147483647, GETDATE())

When I run this, I get: 1950-09-17 07:36:00.107.

So, you want to search for rows in your HBData table where HBDateTime is earlier than that.

Alternatively, you can use DATEDIFF_BIG() instead of DATEDIFF(). It returns the number of seconds as bigint instead of int. It was introduced in SQL Server 2016.

Related Question