SQL Server - DateTime comparison OverFlows INT value

Question

I have a process that compares two DateTime values together to make sure that Activity occurs after Received time. Because we allow 60 seconds of leeway, I previously had a delete statement with the following where clause.

i.ActivityDateTime < i.ReceivedDateTime 
  AND
DATEDIFF(ss, i.ActivityDateTime, i.ReceivedDateTime) > 60

This process ran fine for a long time until we received a time many, many years in the past. Since DateDiff returns an integer, the comparison overflowed the value and the process failed.

A fix I have in place now is

i.ActivityDateTime < i.ReceivedDateTime 
  AND
(
  DATEDIFF(yy, i.ActivityDateTime, i.ReceivedDateTime) > 0
   OR 
  DATEDIFF(ss, i.ActivityDateTime, i.ReceivedDateTime) > 60
)

Testing has gone well with this process so far, but my worry is that I am missing something and, in the future, I may have another failure based on some other combination of Activity and Received time.

Has anyone else experienced something similar, and what other possible comparisons can I perform so that I can make sure no issues happen in the future?

Probably not going to help you at all right now, but in SQL Server 2016 onwards, there's a new function called DATEDIFF_BIG. https://msdn.microsoft.com/en-gb/library/mt628058.aspx — mcr, Feb 26 '16 at 23:02
Does the first answer here help? http://stackoverflow.com/questions/1275208/sql-server-datediff-function-resulted-in-an-overflow — mcr, Feb 26 '16 at 23:03
since you are checking for greather than 1 min, why use DATEDIFF ( MINUTE, date1, date2) > 1 ? — Squirrel, Feb 27 '16 at 01:42
@mcr - Thanks, I did not know about the DateDiff_Big function. I think there is a mistake in the Return Value portion of the text where it gives the same amount for "maximum return values" as the regular DateDiff function. — rdbradshaw, Feb 29 '16 at 15:57
@Squirrel - I think we want to keep the seconds comparison for now, changing to minute definitely bumps the maximum time up (60x higher to be exact) between the two dates. I believe, after testing, that the Year comparison pulls out all datetimes that would overflow either minute or second before the other comparison occurs. — rdbradshaw, Feb 29 '16 at 16:00

score 1 · Answer 1 · answered Mar 05 '16 at 06:54

Instead of using DATEDIFF you could take a different route entirely. Consider the following example.

DECLARE @ActivityDateTime DATETIME, @ReceivedDateTime DATETIME

-- The info was received on this date.
SET @ReceivedDateTime = GETDATE()

-- Let's pretend that the activity ocurred on this date.
-- Adjust the number of seconds to pretend a different date. :)
SET @ActivityDateTime = DATEADD(SECOND, 29, @ReceivedDateTime)

-- If you add 30 seconds to the receive date and it's larger than the
-- receive date then it means that the activity occurred less than
-- 30 seconds after the received date.
IF DATEADD(SECOND, 30, @ReceivedDateTime) > @ActivityDateTime BEGIN
    PRINT 'The activity ocurred more than 30 seconds after the receive date...'
END

I'm not entirely sure that my example meets your exact needs but if not it should be easy to tweak.

Good luck!

SQL Server - DateTime comparison OverFlows INT value

1 Answers1