SQL Server SYSDATETIMEOFFSET() Function

In SQL Server, the SYSDATETIMEOFFSET() function returns the current system date and time along with the time zone offset from Coordinated Universal Time (UTC). This function returns a value of the datetimeoffset data type.

Syntax

The syntax for the SYSDATETIMEOFFSET() function is as follows:

SYSDATETIMEOFFSET()

Usage

The SYSDATETIMEOFFSET() function can be used in scenarios where the system’s current time is needed, and the time zone offset needs to be considered. For example, in a distributed system across different time zones, all timestamps may need to be converted to UTC and stored with the same time zone offset as Coordinated Universal Time. Then, when it is necessary to convert timestamps back to local time, the SYSDATETIMEOFFSET() function can be used to obtain the local time and time zone offset, and then the SWITCHOFFSET() function can be used for conversion.

Examples

Here are two examples of the SYSDATETIMEOFFSET() function:

Example 1

Suppose we need to record the order creation time and time zone offset in an order table. We can use the following SQL statement to insert data:

CREATE TABLE orders (
    id INT PRIMARY KEY,
    created_at DATETIMEOFFSET NOT NULL,
    time_zone VARCHAR(50) NOT NULL
);

INSERT INTO orders (id, created_at, time_zone)
VALUES (1, SYSDATETIMEOFFSET(), 'Eastern Standard Time');

The above SQL statement creates a table called orders, which contains a column called created_at of the DATETIMEOFFSET type to store the order creation time and time zone offset. The SYSDATETIMEOFFSET() function is used to obtain the current system time and time zone offset when inserting data.

Example 2

Suppose we have a table containing date and time data, all of which are based on Coordinated Universal Time. If we need to convert these date and time values to local time, we can use the following SQL statement:

CREATE TABLE events (
    id INT PRIMARY KEY,
    event_time DATETIMEOFFSET NOT NULL
);

INSERT INTO events (id, event_time)
VALUES (1, '2022-03-12 10:00:00 -00:00'),
       (2, '2022-03-12 14:00:00 -00:00');

SELECT id,
  event_time,
  SWITCHOFFSET(
    event_time,
    DATEPART(TZOFFSET, SYSDATETIMEOFFSET())
  ) AS local_time
FROM EVENTS;

The above SQL statement creates a table called events, which contains a column called event_time of the DATETIMEOFFSET type to store the event timestamps. The SWITCHOFFSET() function and SYSDATETIMEOFFSET() function are used to convert the event timestamps to local time.

Conclusion

The SYSDATETIMEOFFSET() function is used in SQL Server to obtain the current system date and time along with the time zone offset. It can be used to record and process timestamps across different time zones, as well as for timestamp conversion in distributed systems across different time zones.