SYSDATETIMEOFFSET Function in SQL Server

In this SQL Server tutorial, you will learn how to use the SYSDATETIMEOFFSET function in SQL Server.

You will learn how to use the SYSDATETIMEOFFSET() function with different time zones and also use it with table columns, especially using it as the default value for recording the exact timing of the data insertion.

SYSDATETIMEOFFSET Function in SQL Server

SQL Server SYSDATETIMEOFFSET is a built-in function that retrieves the current date and time of the system where SQL Server is running with time zone offset. It is essential to record the exact time of data insertion with UTC.

The syntax is given below.

SYSDATETIMEOFFSET();

It is the function alone and doesn’t accept any parameter. The return type of this function is datetimeoffset(7), which combines the data type date and time with a time zone offset.

The SYSDATETIMEOFFSET() is a non-deterministic function, meaning that each time it’s executed, it returns a varying value.

For example, let’s find the current date and time of the system with time zone offset using the query below.

SELECT SYSDATETIMEOFFSET() AS DateAndTimeWithOffSet;
SYSDATETIMEOFFSET Function in SQL Server

When you execute the SYSDATETIMEOFFSET function, it returns the date and time with the timezone off-set as 2023-11-27 16:15:43.8267391 +05:30, where 2023-11-27 is the date, 16:15:43.8267391 is time and +05:30 is time zone offset.

You can get the current date and time with timezone offset using the SYSDATETIMEOFFSET function in SQL Server.

SYSDATETIMEOFFSET Function in SQL Server with At Time Zone

If you want to retrieve the current date and time along with the desired time zone offset, then you need to understand how to change the current timezone to a different timezone; for that, you can use the AT TIME ZONE function in SQL Server.

The syntax is given below.

SYSDATETIMEOFFSET AT TIME ZONE time_zone_name;

Where,

  • SYSDATETIMEOFFSET: The function returns the current date and time with the time zone offset of the current system.
  • AT TIME ZONE time_zone_name: Where time_zone_name can be any timezone you want to use.

Now, let’s retrieve the current date and time of Pacific Standard Time using the below query.

SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'Pacific Standard Time' as PacificTime;
SYSDATETIMEOFFSET Function in SQL Server with At Time Zone

As you can see in the above output, the date and time with time zone offset are 2023-11-27 03:02:13.5426376 -08:00, where -08:00 is the Pacific Standard Time you can see in the above output.

In the query part AT TIME ZONE ‘Pacific Standard Time’ means convert the current system time to Pacific Standard Time.

This is how to use the SYSDATETIMEOFFSET function in SQL Server with the AT TIME ZONE function to find the current date and time of the system with the desired time zone.

SYSDATETIMEOFFSET Function in SQL Server with Table

You can record the exact timing of the query execution; suppose you have an application and work as a backend developer. The application needs to log user activities.

As you know, recording the exact time of these activities with time zone is very important for auditing and tracking purposes.

First, create a new UserActivityLog table using the query below.

CREATE TABLE UserActivityLog (

	UserID INT,
	Activity NVARCHAR(255),
	ActivityTime DATETIMEOFFSET DEFAULT SYSDATETIMEOFFSET()
);

While specifying the table column ActivityTime, the default value is set to the SYSDATETIMEOFFSET() function; if the value is not provided, it automatically inserts the current system time in that column for the specific user activity.

Now, insert the data into UserActivityLog using the query below one by one.

INSERT INTO UserActivityLog (UserID, Activity)
VALUES (1, 'Logged in');

INSERT INTO UserActivityLog (UserID, Activity)
VALUES (2, 'Logged out');

INSERT INTO UserActivityLog (UserID, Activity)
VALUES (3, 'Updated profile picture');

INSERT INTO UserActivityLog (UserID, Activity)
VALUES (4, 'Changed the email address');
SYSDATETIMEOFFSET Function in SQL Server with UserActivityLog Table

View the UserActivityLog table using the below query.

SELECT * FROM UserActivityLog;
SYSDATETIMEOFFSET Function in SQL Server with Table

The table contains the user activity timing in the ActivityTime column that you can see in the output. For example, the user with ID equal to 3 updated profile picture at 2023-11-27 16:58:09.6233996 +05:30.

And that time is the exact time with the time zone offset. This is how you can use the SYSDATETIMEOFFSET function in SQL Server with a table.

Conclusion

In this SQL Server tutorial, you covered how to retrieve the exact date and time of the system using the SYSDATETIMEOFFSET() function of SQL Server. Additionally, you learned how to use the SYSDATETIMEOFFSET() function to find the system time of the desired time zone and use that function with table columns.

You may like to read: