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.