SYSUTCDATETIME Function in SQL Server

by

In this SQL Server tutorial, you will learn about the SYSUTCDATETIME function in SQL Server.

Then, you will understand where this function can be very helpful and the syntax of the SYSUTCDATETIME() function. Finally, you will learn to use the SYSUTCDATETIME() function with the table.

SYSUTCDATETIME Function in SQL Server

In SQL Server, many functions can handle the date and time, and one of the functions is SYSUTCDATETIME(), which obtains the current date and time of the computer where the SQL Server instance is running.

Generally, this function is used in the Global label application because it can return the date/time of different time zones. The SYSUTCDATETIME() function is unlike the GETDATE() function, which only returns the date/time in the local time zone.

The SYSUTCDATETIME() function returns the date and time in the UTC (Coordinated Universal Time), with fractional seconds ranging from 1 to 7 digits.

The syntax is given below.

SYSUTCDATETIME()

Where SYSUTCDATETIME() is the only function and doesn’t accept any parameters, the return type of this function is datetime2, which is the datatype for the date and time value.

The SYSUTCDATETIME() returns the exact date and time value whenever it is called, so it is called a non-deterministic function. If this function is non-deterministic, it makes indexing views or expressions that include this function impossible.

With an example, let’s see how to use the SYSUTCDATETIME() function, and for that, execute the query below.

SELECT SYSUTCDATETIME() AS UTCDateTime;
SYSUTCDATETIME Function in SQL Server

From the above output, you can see the datetime value returned by the SYSUTCDATETIME() function is 2023-11-28 08:07:59.4974683.

It returns the timestamp value in the format ‘YYYY-MM-DD hh:mm:ss.nnnnnnn’, the standard SQL format for datetime data types.

Where,

  • YYYY: It represents the four-digit year, for example, 2023.
  • MM: It represents the two-digit month, for example, 11.
  • DD: It represents the two-digit day, for example, 28.
  • hh: It means the two-digit hour in 24-hour format, for example, 08 hours.
  • mm: It represents the two-digit minutes, for example, 07 minutes.
  • ss: It represents the two-digit seconds, for example, 59 seconds.
  • nnnnnnn: It means the fractional seconds (milliseconds), seven-digit decimal places long, for example, 4974683 fractional seconds.

This is how to use the SYSUTCDATETIME function in SQL Server.

SYSUTCDATETIME Function in SQL Server with Table

You can use the SYSUTCDATETIME() function with a table or create a table and define any column default value as the SYSUTCDATETIME() function. Whenever a new record is inserted in that table, it will insert the current date/time in UTC.

But here, you will not do that. Instead, use the SYSUTCDATETIME() function with INSERT statement.

First, create a GlobalSalesData table with columns SaledID, ProductID, and SaleTime. This table records the sale time of each sold product in different time zones.

For that, use the below query.

CREATE TABLE GlobalSalesData (
	SaleID INT,
	ProductID INT,
	SaleTime DATETIME2(7)
);

Insert the below data into the GlobalSalesData, or you can think of it as the sale is happening.

INSERT INTO GlobalSalesData (SaleID, ProductID, SaleTime) VALUES (101, 10, SYSUTCDATETIME());
INSERT INTO GlobalSalesData (SaleID, ProductID, SaleTime) VALUES (102, 15, SYSUTCDATETIME());
INSERT INTO GlobalSalesData (SaleID, ProductID, SaleTime) VALUES (103, 20, SYSUTCDATETIME());

Now, view the table using the below query.

SELECT * FROM GlobalSalesData;
SYSUTCDATETIME Function in SQL Server with Table

As you can see, the result contains three records representing the global sales record, and the column SaleTime contains the exact sale date and time in UTC format according to the current timezone.

Notice the sale time here and change your system’s time zone to a different zone, as shown below.

SYSUTCDATETIME Function in SQL Server with Table Setting Different Time Zone

As you can see, I have changed my system time zone to (UTC + 09:00) Seoul. Now insert the below records.

INSERT INTO GlobalSalesData(SaleID, ProductID, SaleTime) VALUES (104, 25, SYSUTCDATETIME());
INSERT INTO GlobalSalesData (SaleID, ProductID, SaleTime) VALUES (105, 30, SYSUTCDATETIME());
INSERT INTO GlobalSalesData (SaleID, ProductID, SaleTime) VALUES (107, 35, SYSUTCDATETIME());
SYSUTCDATETIME Function in SQL Server with Table Different Time Zone

Look at the SaleID from 104 to 106 with different dates and times in UTC. This time date/time is different because of different time zones.

You can use the SYSUTCDATETIME() function with an INSERT statement to insert the exact date and time in UTC format.

Conclusion

In this SQL Server tutorial, you learned how to find the current system time in UTC using the SYSUTCDATETIME() function in SQL Server. Then, you learned to insert the exact date and time value in UTC into the table.

You may like to read: