SYSDATETIME going backwards

Question

First up, I have solved the actual problem that I had, by moving to using a datetime2, and DATEDIFFing at the MICROSECOND scale. But I'm still surprised by the behaviour I was seeing, so ....

I'm trying to diagnose a perf issue with some SQL, and I have code that loops like this to test it:

DECLARE @timer datetime = NULL
DECLARE @diff int = 0
DECLARE @total int = 0

WHILE(<condition>)
BEGIN
    SET @timer = SYSDATETIME()
    <select statement under test>
    SET @diff = DATEDIFF(MILLISECOND, @timer, SYSDATETIME())
    SET @total = @total + @diff
END

SELECT @total

I'm getting NEGATIVE totals, though !?

How the hell is that a thing? I would understand ending up with @total = 0; that's saying that the increments are <1 ms (or close enough that the resolution of the timer reports that, anyway) so that I end up summing lots of 0s.

But it seems enormously bugged for it to possible for the DATEDIFF to return negative!

I checked further and confirmed that the @diff variable is indeed returning -1 and even -2.

What gives?

@GordonLinoff True. Corrected. They were of course initialised in the real code that I have trimmed down. — Brondahl, Apr 15 '17 at 13:46

Martin Smith · Accepted Answer · 2017-04-15T14:19:34.990

2

SYSDATETIME returns a datetime2 with 7digits of precision.

You are assigning it to a datetime variable with only precision of approx 1/300 per second. So it can get rounded up (or down but up is the cause here).

Try

DECLARE @SYSDATETIME DATETIME2 = '2017-04-15 14:49:36.001999'
DECLARE @timer DATETIME = @SYSDATETIME;
DECLARE @diff INT = DATEDIFF(MILLISECOND, @timer, @SYSDATETIME);

SELECT @timer AS [@timer],
       @diff  AS [@diff];

This returns

+-------------------------+-------+
|         @timer          | @diff |
+-------------------------+-------+
| 2017-04-15 14:49:36.003 |    -2 |
+-------------------------+-------+

The .001999 gets rounded to .003 and that crosses two milisecond boundaries so DATEDIFF(MILLISECOND returns -2 when comparing the rounded and non rounded values.

edited Apr 15 '17 at 14:19

answered Apr 15 '17 at 13:59

Martin Smith

438,706
87
741
845

This seems like a classic answer. Hope if receives many upvotes. – Gordon Linoff Apr 16 '17 at 00:04
This answer also suggests that I don't correctly understand how `DATEDIFF` calculates it's answers? I would have expected `DATEDIFF(unit, 1.99, 3)` to return `1`, not `2`. Is it essentially going from `Floor_by_datepart(start)` to `Floor_by_datepart(end)` – Brondahl Apr 16 '17 at 08:25
@Brondahl it returns the number of datepart boundaries crossed. `DATEDIFF(YEAR, '20161231 23:59', '20170101 00:00')` returns 1 year even though it is only a minute. So yes. – Martin Smith Apr 16 '17 at 08:58

SYSDATETIME going backwards

1 Answers1