0

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.

  • It sounds as sometimes you get data from another table, maybe there is HBData table in different schemas? – Denis Rubashkin Oct 5 '18 at 8:54
  • Can you post some sample data that generates this error? (@hbdatetime and getdate()) – McNets Oct 5 '18 at 9:17
  • Hi, the table is only 2 columns and has only 1 row of data ID HBDateTime 1 2018-10-05 12:09:28.000 – Stefan Grönberg Oct 5 '18 at 10:11
  • Declare @diffSec as bigint or decimal(38,0). See what value is actually in there. If it's more than two billion-ish you've found your problem. – Michael Green Oct 5 '18 at 12:30
4

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.

  • Hi Tibor. The HBData table has only 1 row with 2 columns, and there is a program that updates the id=1 with current time and date. The program updates this row every 1 seconds roughly. so the data in the table and the getdate() in the mssql should in reality not differ more then a second or so. Before i had only getdate() without removing the miliseconds, but it didnt help. ID HBDateTime 1 2018-10-05 12:09:28.000 – Stefan Grönberg Oct 5 '18 at 10:14
  • 3
    Unfortunately, you do have occurrences where the date is evidently a lot earlier than 2018 (earlier then 1950 approx). The question is how to proceed. Use DATEDIFF_BIG() instead? Add a check if the date is old and throw an error? Or try to catch when the date is set to such an old value on the HBData table in the first place (using a trigger, for instance). Only you can say which is the right action for you... – Tibor Karaszi Oct 5 '18 at 10:46
  • There should not be able to have old dates when the program that updates the row uses systems current time, and the database also uses current systemtime (altho in testing the db is in a VM on the same computer). Have to check if i can log the sql statement on the server side when an error occurs so i can see the actual values. – Stefan Grönberg Oct 5 '18 at 10:50
  • 1
    @StefanGrönberg should not be able to, perhaps, but in reality, that is what is happening, even if you refuse to believe it. – Aaron Bertrand Oct 5 '18 at 11:38
  • @StefanGrönberg, a simple solution would be to add a trigger with a "sanity check" which logs when a value "out of bounds" is inserted. – Tibor Karaszi Oct 5 '18 at 11:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.