Michael J. Swart

RSS Feed
RSS Feed
Email Me
Email Me
About Me
About Me

December 20, 2017

When Measuring Timespans, try DATEADD instead of DATEDIFF

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 11:25 am

Recently I tackled an issue where a DateTime field was getting updated too often. The query looked something like this:

UPDATE dbo.SOMETABLE
SET MyDateTime = GETUTCDATE()
WHERE SomeTableId = @SomeTableId;

So I decided to give up accuracy for concurrency. Specifically, I decided to only update MyDateTime if the existing value was more than a second ago.

First Attempt: Use DATEDIFF With SECOND

My first attempt looked like this:

UPDATE dbo.SOMETABLE
SET MyDateTime = GETUTCDATE()
WHERE SomeTableId = @SomeTableId
AND DATEDIFF(SECOND, GETUTCDATE(), MyDateTime) > 1;

But I came across some problems. I assumed that the DATEDIFF function I wrote worked this way: Subtract the two dates to get a timespan value and then return the number of seconds (rounded somehow) in that timespan.

But that’s not how it works. The docs for DATEDIFF say:

“Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.”

There’s no rounding involved. It just counts the ticks on the clock that are heard during a given timespan.

Check out this timeline. It shows three timespans and the DATEDIFF values that get reported:

But that’s not the behavior I want.

How About DATEDIFF with MILLISECOND?

Using milliseconds gets a little more accurate:

UPDATE dbo.SOMETABLE
SET MyDateTime = GETUTCDATE()
WHERE SomeTableId = @SomeTableId
AND DATEDIFF(MILLISECOND, GETUTCDATE(), MyDateTime) > 1000;

And it would be good for what I need except that DATEDIFF using MILLISECOND will overflow for any timespan over a month. For example,

SELECT DATEDIFF (millisecond, '2017-11-01', '2017-12-01')

gives this error:

Msg 535, Level 16, State 0, Line 1
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.

SQL Server 2016 introduced DATEDIFF_BIG to get around this specific problem. But I’m not there yet.

Use DATEADD

I eventually realized that I don’t actually need to measure a timespan. I really just need to answer the question “Does a particular DateTime occur before one second ago?” And I can do that with DATEADD

UPDATE dbo.SOMETABLE
SET MyDateTime = GETUTCDATE()
WHERE SomeTableId = @SomeTableId
AND MyDateTime < DATEADD(SECOND, -1, GETUTCDATE()) ;

Update: Adam Machanic points out another benefit to this syntax. The predicate AND MyDateTime < DATEADD(SECOND, -1, GETUTCDATE()) syntax is SARGable (unlike the DATEDIFF examples). Even though there might not be a supporting index or SQL Server might not choose to use such an index it in this specific case, I prefer this syntax even more.

So How About You?

Do you use DATEDIFF at all? Why? I'd like to hear about what you use it for. Especially if you rely on the datepart boundary crossing behavior.

7 Comments »

  1. I ran into a similar problem, though with a different solution here:https://dba.stackexchange.com/questions/132437/sargable-where-clause-for-two-date-columns

    I could have sworn you commented on it at some point.

    Comment by Erik Darling — December 20, 2017 @ 6:47 pm

  2. If you somehow in some small way confused me with Paul White, I’ll take that as a compliment.

    Comment by Michael J. Swart — December 20, 2017 @ 7:41 pm

  3. I use a combo of datepart and datediff all the time for doing counts in ranges:
    days – select dateadd(dd,(datediff(dd, 0,tstamp),0)….
    hours – select dateadd(hh,(datediff(hh, 0,tstamp),0)….
    minutes in groups of 10 – select dateadd(hh,(10*(datediff(hh, 0,tstamp)/10),0)….

    Depending on the time unit as you noted I sometimes rebase it to a time before the period I’m reporting. Of course this has to be plotted as X,Y because if nothing happened there’s no points unless I have a calendar of the right grain to join to.

    Comment by Gareth Husk — December 20, 2017 @ 11:49 pm

  4. […] Michael J. Swart points out a bit of trickery with DATEDIFF: […]

    Pingback by Using DATEADD Instead Of DATEDIFF – Curated SQL — December 21, 2017 @ 8:05 am

  5. “…boundaries crossed…”

    I sometimes wonder who designed all these bad SQL functions. Who thought this was a good idea? There are many such design errors in SQL/T-SQL.

    Comment by tobi — December 24, 2017 @ 10:54 am

  6. @Gareth, I see what you mean. You use them to calculate aggregate categories (like histogram buckets).
    As a matter of style, I often do the same thing using DATEPART. For example, If I want to group something by hour, my GROUP BY statement becomes something like:

    /* Days    */ GROUP BY CAST(myDateTime AS DATE) ...
/* Hours   */ GROUP BY CAST(myDateTime AS DATE), DATEPART(HOUR, myDateTime) ...
/* Minutes */ GROUP BY CAST(myDateTime AS DATE), DATEPART(HOUR, myDateTime), DATEPART(MINUTE, myDateTime) ...

    It also suffers from the reporting issues you mentioned.

    Comment by Michael J. Swart — December 27, 2017 @ 2:07 pm

  7. @tobi

    I can’t even guess why they would define it that way. The best I could think of is that maybe it makes sense when you’re subtracting things like Months where a fraction of a month (like 1/2 a month) is not a useful thing.

    But it’s almost never useful for things like seconds, minutes or hours.

    After some digging, it seems like Datediff seems to mimic the behavior of Visual Basic’s Datediff which has been around since at least the 90s. https://msdn.microsoft.com/en-us/library/aa227591(v=vs.60).aspx

    Comment by Michael J. Swart — December 27, 2017 @ 2:29 pm

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress