42

According to MSDN, Getdate(), GetUtcDate(), and CURRENT_TIMESTAMP all return DATETIME. I ran a short test, which confirms that:

CREATE TABLE #t(T DATETIME2(7));
GO

DECLARE @i INT ;
SET @i=1;

WHILE @i<10000 BEGIN ;
INSERT #t VALUES(CURRENT_TIMESTAMP) ;
SET @i=@i+1;
END ;

SELECT DISTINCT t 
FROM #t 
ORDER BY t ;

---

2013-01-28 13:23:19.4930000
2013-01-28 13:23:19.4970000
2013-01-28 13:23:19.5000000
2013-01-28 13:23:19.5030000
2013-01-28 13:23:19.5070000
2013-01-28 13:23:19.5100000
2013-01-28 13:23:19.5130000

(snip)

Is there a similar function that returns DATETIME2(7)?

53

SYSDATETIME returns a DATETIME2 object.

CREATE TABLE #t(T DATETIME2(7));
GO

DECLARE @i INT ;
SET @i=1;

WHILE @i<10000 BEGIN ;
INSERT #t VALUES(SYSDATETIME()) ;
SET @i=@i+1;
END ;

SELECT DISTINCT t 
FROM #t 
ORDER BY t ;



2013-01-28 12:34:28.2514394
2013-01-28 12:34:28.2670399
2013-01-28 12:34:28.2826404
2013-01-28 12:34:28.2982409
2013-01-28 12:34:28.3138414
2013-01-28 12:34:28.3294419
2013-01-28 12:34:28.3450424
2013-01-28 12:34:28.3606429
2013-01-28 12:34:28.3762434
2013-01-28 12:34:28.3918439
2013-01-28 12:34:28.4074444
2013-01-28 12:34:28.4230449
2013-01-28 12:34:28.4386454
2013-01-28 12:34:28.4542459
2013-01-28 12:34:28.4698464
  • 13
    There is also SYSUTCDATETIME() to complement GETUTCDATE(). – Aaron Bertrand Jan 28 '13 at 19:36
  • 1
    @swasheck, good example! it clearly shows that although the resolution of this function seems to be better, it still returns values which are about 16ms apart. – Razvan Socol Feb 24 '17 at 12:19
  • I have essentially written the same loop, although I have 5 columns in my table and I loop 100 times. The first 33 rows show exactly the same datetime2 value - including all 7 decimal places. The next 29 lines show the same datetime2 value as each other, and so on. Can you suggest why this might be so? I recall in a different environment some time back learning that although high precision time-related data types are accurate to so many decimal places, the system clock on the machine running the query does not necessarily update the system time every 0.000001 seconds. Could this be the cause? – youcantryreachingme Sep 26 '18 at 4:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.