GETUTCDATE and SYSUTCDATETIME: Which Current Time Function Returns What

GETUTCDATE and SYSUTCDATETIME return the current UTC time, and GETDATE and SYSDATETIME return local time. The two pairs also differ in data type and precision. This post runs all four side by side and shows which one to use for a time stamp.

Four Functions, One Query

Run the four functions in one query. The fifth column is SYSDATETIMEOFFSET, which returns the local time together with its offset from UTC.

SELECT GETDATE() AS LocalDatetime,
  SYSDATETIME() AS LocalDatetime2,
  GETUTCDATE() AS UtcDatetime,
  SYSUTCDATETIME() AS UtcDatetime2,
  SYSDATETIMEOFFSET() AS LocalWithOffset;

The local columns read 19:28 and the UTC columns read 13:58 for the same moment. Look at the digits after the seconds: GETDATE shows .813, and SYSDATETIME shows .8167589. The next query measures the gap.

SELECT DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()) AS MinutesAheadOfUtc,
  DATEPART(TZOFFSET, SYSDATETIMEOFFSET()) AS OffsetMinutes;

The test machine’s local clock is 330 minutes ahead of UTC, which is 5 hours 30 minutes. The server reads that gap from the time zone of its operating system. A server in another time zone shows another gap, and daylight saving time can change it. UTC never changes with the seasons, so a UTC stamp means the same thing all year.

SYSDATETIMEOFFSET is the only one of the five that carries the offset with the value, +05:30 in this run. The other four return a bare date and time, so the zone is lost once you store the value. That’s one more reason to store UTC.

Datetime or Datetime2

The two pairs also return different types. SQL_VARIANT_PROPERTY shows the type and the number of fraction digits for each function.

SELECT 'GETDATE' AS FunctionName, SQL_VARIANT_PROPERTY(GETDATE(), 'BaseType') AS DataType, SQL_VARIANT_PROPERTY(GETDATE(), 'Scale') AS FractionDigits
UNION ALL SELECT 'SYSDATETIME', SQL_VARIANT_PROPERTY(SYSDATETIME(), 'BaseType'), SQL_VARIANT_PROPERTY(SYSDATETIME(), 'Scale')
UNION ALL SELECT 'GETUTCDATE', SQL_VARIANT_PROPERTY(GETUTCDATE(), 'BaseType'), SQL_VARIANT_PROPERTY(GETUTCDATE(), 'Scale')
UNION ALL SELECT 'SYSUTCDATETIME', SQL_VARIANT_PROPERTY(SYSUTCDATETIME(), 'BaseType'), SQL_VARIANT_PROPERTY(SYSUTCDATETIME(), 'Scale')
UNION ALL SELECT 'SYSDATETIMEOFFSET', SQL_VARIANT_PROPERTY(SYSDATETIMEOFFSET(), 'BaseType'), SQL_VARIANT_PROPERTY(SYSDATETIMEOFFSET(), 'Scale');

GETDATE and GETUTCDATE return datetime, with 3 fraction digits. SYSDATETIME and SYSUTCDATETIME return datetime2, with 7. SYSDATETIMEOFFSET returns datetimeoffset, also with 7. So the question “which function gives local time in milliseconds?” has one answer: GETDATE. SYSDATETIME goes finer than milliseconds.

Why GETDATE Ends in 0, 3 or 7

A datetime value can’t hold every millisecond. It stores time in steps of 1/300 of a second, about 3.33 milliseconds. This loop reads both local functions 5,000 times and counts the last digit of each fraction.

SET NOCOUNT ON;
DECLARE @Readings TABLE (OldStyle DATETIME NOT NULL, NewStyle DATETIME2(7) NOT NULL);
DECLARE @Count INT = 0;
WHILE @Count < 5000
BEGIN
  INSERT INTO @Readings (OldStyle, NewStyle) VALUES (GETDATE(), SYSDATETIME());
  SET @Count += 1;
END;
SELECT RIGHT(CONVERT(VARCHAR(23), OldStyle, 121), 1) AS LastDigit, COUNT(*) AS Readings
FROM @Readings
GROUP BY RIGHT(CONVERT(VARCHAR(23), OldStyle, 121), 1)
ORDER BY LastDigit;

SELECT COUNT(DISTINCT RIGHT(CONVERT(VARCHAR(27), NewStyle, 121), 1)) AS DifferentLastDigits FROM @Readings;

GETDATE ended in 0, 3 or 7 and nothing else: 1936, 1427 and 1637 readings. SYSDATETIME ended in all 10 possible digits. If you compare time stamps that need finer detail than 3 milliseconds, use the datetime2 functions.

Storing a Time Stamp

A table can fill the time stamp for you. These two DEFAULT constraints record the same moment in UTC and in local time.

CREATE TABLE Visits (
  VisitID INT IDENTITY(1,1) PRIMARY KEY,
  Visitor VARCHAR(20) NOT NULL,
  VisitedUtc DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
  VisitedLocal DATETIME2(3) NOT NULL DEFAULT SYSDATETIME()
);
INSERT INTO Visits (Visitor) VALUES ('Asha'), ('Ben');
GO

SELECT VisitID, Visitor, VisitedUtc, VisitedLocal, DATEDIFF(MINUTE, VisitedUtc, VisitedLocal) AS MinutesApart FROM Visits ORDER BY VisitID;

Both visitors show the same pair of values, 13:58 UTC and 19:28 local, 330 minutes apart. Local time depends on the zone of the server that wrote the row. Store UTC, and convert to a local time zone when you show the value.

Converting UTC When You Display It

AT TIME ZONE turns a stored UTC value into the time zone of the person reading it. The first AT TIME ZONE tells SQL Server the value is UTC, and the second converts it. The zone names are Windows names.

DECLARE @Now DATETIME2(0) = SYSUTCDATETIME();
SELECT @Now AS UtcTime, @Now AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS PacificTime;

UTC reads 13:58, and Pacific time reads 06:58 with an offset of -07:00. That offset is right for this date because daylight saving time is in effect. The conversion knows the rules, so you don’t have to store them.

Clean Up

Remove the sample table when you finish.

DROP TABLE Visits;

What to Remember

Use SYSUTCDATETIME for a stored time stamp, and SYSDATETIME when you need local time to a fine precision. GETDATE and GETUTCDATE remain correct, but they return datetime with the 3.33 millisecond steps.

To go further, read GETDATE vs SYSDATETIME: Precision of the Current Time, Storing UTC and Local Time Side by Side and AT TIME ZONE Twice: Convert UTC to Local Time in SQL Server.

A time stamp is not a clock reading, it is a moment that needs a time zone.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.