0

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?

See this fiddle.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Himanshu
  • 31,810
  • 31
  • 111
  • 133
  • What does `SELECT DATEDIFF(ms, GETDATE(), GETDATE());` return? – Waleed Khan Aug 18 '12 at 04:53
  • The engine might be smart enough to cache two identical expressions. – McGarnagle Aug 18 '12 at 04:54
  • 1
    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. Why would you need to do this, just out of curiosity? – Ken White Aug 18 '12 at 05:06
  • @KenWhite If we execute `SELECT DATEDIFF(ms, GETDATE(), GETDATE())` will GetDate() be executed twice or once? – Himanshu Aug 18 '12 at 05:10
  • Most likely once, because the DBMS is smart enough to figure out you're calling the same function. You're not doing that, though - you're calling two different functions, that execute two different code paths. – Ken White Aug 18 '12 at 05:13
  • @KenWhite you are correct. DBMS does not execute the same function twice. [See this fiddle](http://www.sqlfiddle.com/#!3/cfab1/13). The result of the second query is always `0`. – Himanshu Aug 18 '12 at 05:18
  • @hims056 - It isn't **always** zero. It is just much more likely to be zero. – Martin Smith Aug 18 '12 at 08:42
  • @Downvoter. Your feedback please.... – Himanshu Dec 11 '12 at 08:55
  • 1
    @hims056 in the future please keep in mind that it is not always difficult to figure out who is going on an unjustified down-voting spree... – Aaron Bertrand Jan 29 '13 at 05:46

3 Answers3

9

They are two different function calls that can return two different times.

Additionally GETDATE returns a datetime datatype which only has precision of 3-4 ms whereas SYSDATETIME() returns a datetime2(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.

DECLARE @D1 DATETIME2 = '2012-08-18 10:08:40.0650000'
DECLARE @D2 DATETIME = @D1 /*Rounded to 2012-08-18 10:08:40.067*/
SELECT DATEDIFF(ms, @D1 , @D2) /*Returns 2*/

The other answer is incorrect that if you substitute in GETDATE() the function is only called once as can be demonstrated from the below.

WHILE DATEDIFF(ms, GETDATE() , GETDATE()) = 0 
PRINT 'This will not run in an infinite loop'

When running a loop on my windows XP desktop with GETDATE() and SYSDATETIME I can also see results that indicate that something else might be going on as well though. Perhaps calling a different API.

CREATE TABLE #DT2
  (
     [D1] [DATETIME2](7),
     [D2] [DATETIME2](7)
  )

GO

INSERT INTO #DT2
VALUES(Getdate(), Sysdatetime())

GO 100

SELECT DISTINCT [D1],
                [D2],
                Datediff(MS, [D1], [D2]) AS MS
FROM   #DT2

DROP TABLE #DT2

Example results below

+-----------------------------+-----------------------------+-----+
|             D1              |             D2              | MS  |
+-----------------------------+-----------------------------+-----+
| 2012-08-18 10:16:03.2500000 | 2012-08-18 10:16:03.2501680 |   0 |
| 2012-08-18 10:16:03.2530000 | 2012-08-18 10:16:03.2501680 |  -3 |
| 2012-08-18 10:16:03.2570000 | 2012-08-18 10:16:03.2501680 |  -7 |
| 2012-08-18 10:16:03.2600000 | 2012-08-18 10:16:03.2501680 | -10 |
| 2012-08-18 10:16:03.2630000 | 2012-08-18 10:16:03.2501680 | -13 |
| 2012-08-18 10:16:03.2630000 | 2012-08-18 10:16:03.2657914 |   2 |
| 2012-08-18 10:16:03.2670000 | 2012-08-18 10:16:03.2657914 |  -2 |
| 2012-08-18 10:16:03.2700000 | 2012-08-18 10:16:03.2657914 |  -5 |
| 2012-08-18 10:16:03.2730000 | 2012-08-18 10:16:03.2657914 |  -8 |
| 2012-08-18 10:16:03.2770000 | 2012-08-18 10:16:03.2657914 | -12 |
| 2012-08-18 10:16:03.2800000 | 2012-08-18 10:16:03.2814148 |   1 |
+-----------------------------+-----------------------------+-----+

The rows of interest are 

| 2012-08-18 10:16:03.2600000 | 2012-08-18 10:16:03.2501680 | -10 |
| 2012-08-18 10:16:03.2630000 | 2012-08-18 10:16:03.2501680 | -13 |

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 reports 10:16:03.26X whereas SYSDATETIME reports 10:16:03.250

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • It seems also the functions are using a different precision, as in D1 the last four digits are always zero. That is not true for D2, where all the seven digits seem significative. – apaderno Aug 19 '12 at 12:06
  • 1
    @kiamlaluno - Yes. `datetime` datatype only has precision of 300 ticks per second. i.e up to 3 digits of precision with last digit `0`, `3` or `7`. The newer `datetime2` datatype can represent up to 7 decimal digits of precision. – Martin Smith Aug 19 '12 at 12:17
1

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. Using GetDate() and SysDateTime() 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 and 1 + 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 to 1 + Rand() and 1 + Rand(), you have no way of knowing what the two different calls to Rand() will return, so you have to do the calculations separately.

Ken White
  • 123,280
  • 14
  • 225
  • 444
  • This answer is misleading. Even if the two functions could be called at the same time, the results (to the millisecond) would often/usually differ. And indeed, an earlier GETDATE() call could return a time later than a subsequent SYSDATETIME(), and vice versa. – brewmanz Feb 06 '17 at 19:34
  • @brewmanz: How is it misleading? It says precisely that they're never going to produce the same results. Did you read the first sentence and then stop? – Ken White Feb 06 '17 at 20:15
  • Several misleading reasons: A) They CAN produce the same results, and I can predict when those occasions would be B) When the results differ, it is NOT because the two functions cannot be called simultaneously C) It is NOT the same as using Rand(); the result is deterministic and given the result of the SysDateTime(), I'm in with a good chance of predicting what the result of an adjacent GetDate() call would be. – brewmanz Feb 08 '17 at 05:21
-1

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.

brewmanz
  • 1,181
  • 11
  • 17