USE of SYSDATETIMEOFFSET function in SQL Server

The SYSDATETIMEOFFSET () function returns the current date and time of the system including the time zone offset from UTC. It’s useful when we need date-time with time zone. Let’s see the example where we can use this function. Suppose we need to create an application which is used by users in multiple time zones (e.g., India, USA, Europe).

Let see the demo

Creating a table

CREATE TABLE EmpLoginAudit (     Emp_Audit_ID BIGINT IDENTITY,     Emp_User_Name VARCHAR(100),     Emp_Login_Time DATETIMEOFFSET DEFAULT SYSDATETIMEOFFSET() );

 Table created successfully. Now inserting some records.

INSERT INTO EmpLoginAudit (Emp_User_Name) VALUES ('Bagesh');  INSERT INTO EmpLoginAudit (Emp_User_Name) VALUES ('Rajesh');

See the data

We can see the Login time is our time zone. We need to AT TIME ZONE clause is used to convert a datetime or datetimeoffset value from one time zone to another, or to add time zone awareness to a datetime. It supports on SQL Server 2016 and later version.

SELECT Emp_User_Name, Emp_Login_Time,     Emp_Login_Time AT TIME ZONE 'India Standard Time' as India_Standard_Time,      Emp_Login_Time AT TIME ZONE 'UTC' AS UTC_LoginTime,     Emp_Login_Time AT TIME ZONE 'Nepal Standard Time' AS Nepal_Standard_Time FROM EmpLoginAudit;

We will get the list of time zone in the “sys.time_zone_info” Table in SQL server.

SELECT * FROM sys.time_zone_info;

See the below example

SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'India Standard Time' AS [India Standard Time --> My Local Time] GO SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'GMT Standard Time' AS [GMT Standard Time] GO SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'US Mountain Standard Time' AS [US Mountain Standard Time] GO SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'Central Asia Standard Time' AS [Central Asia Standard Time] GO SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'AUS Central Standard Time' AS [AUS Central Standard Time]