I am trying to get Datediff
between GETDATE()
and SYSDATETIME()
in milliseconds.
SELECT DATEDIFF(ms, GETDATE() , SYSDATETIME());
The result I am getting is 0
or 1
or 2
or 3
. What is the reason for this difference?
I am trying to get Datediff
between GETDATE()
and SYSDATETIME()
in milliseconds.
SELECT DATEDIFF(ms, GETDATE() , SYSDATETIME());
The result I am getting is 0
or 1
or 2
or 3
. What is the reason for this difference?
This difference is a good example of the difference between PRECISION and RESOLUTION (let's leave ACCURACY to one side for the moment). GETDATE() returns a DATETIME with (apparently) a PRECISION to the millisecond, but, if you put it in a tight loop, you'll find the next different value returned is several milliseconds later; it can only return about 300 different values each second, as its RESOLUTION is only to about 3 or 4 milliseconds. read more about this here This is a design feature/compromise of the DATETIME datatype.
They are two different function calls that can return two different times.
Additionally
GETDATE
returns adatetime
datatype which only has precision of 3-4 ms whereasSYSDATETIME()
returns adatetime2(7)
datatype.Even if both calls were to return exactly the same time you could see the issue that you are experiencing due to rounding.
The other answer is incorrect that if you substitute in
GETDATE()
the function is only called once as can be demonstrated from the below.When running a loop on my windows XP desktop with
GETDATE()
andSYSDATETIME
I can also see results that indicate that something else might be going on as well though. Perhaps calling a different API.Example results below
The rows of interest are
This discrepancy is too large to be a rounding issue and can't just be a timing issue with a delay between calling the two functions as the issue exists on more than one row that
GETDATE
reports10:16:03.26X
whereasSYSDATETIME
reports10:16:03.250
They differ because the two functions can't be called simultaneously (at the exact same time). Other processes running can affect the timings. There are dozens of reasons they can differ by varying amounts.
If you do the same thing with two calls to
GetDate()
instead, they result in no difference, because the database engine is smart enough to figure out they're the same thing and re-use the results. UsingGetDate()
andSysDateTime()
is different, though, because they're not the same code path (they do different things).Think of it this way: If you see
1 + 2
and1 + 2
, it's easy to see that the first expression and the second are the same, and so you only have to do the calculation once. If you change it to1 + Rand()
and1 + Rand()
, you have no way of knowing what the two different calls toRand()
will return, so you have to do the calculations separately.