Multiple SYSDATETIME In The Same SELECT May Give Unexpected Results

Louis Davidson walks through a scenario he experienced:

The data is exactly as expected, even though the other two calls would have returned .902 and .903 if simply rounded off. On the other hand, looking for differences between the time1_3 and time2_3 columns:

SELECT * FROM #hold WHERE DATEDIFF(MILLISECOND,time1_3,time2_3) <> –1;

Returns 133 rows. With the sysdatetime values being exactly the same:

sysdatetime1                sysdatetime2 ————————— ————————— 2018–06–18 20:53:21.0443399 2018–06–18 20:53:21.0443399 2018–06–18 20:53:21.0493653 2018–06–18 20:53:21.0493653

But the other columns, are incorrect for our needs, as the values are the same:

time1_3                     time2_3 ————————— ————————— 2018–06–18 20:53:21.045     2018–06–18 20:53:21.045 2018–06–18 20:53:21.050     2018–06–18 20:53:21.050

This was the bug we experienced! Instead of being 1 millisecond different, the values were equal.

Louis’s moral to the story is to test your assumptions.  The more prosaic moral is that calls to get the current time take a non-zero amount of time.