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.