Convert UTC DateTime to different time zones in SQL Server

When working on SQL Server databases with multi time zone users, it is always good to store date and time in UTC (Universal Time Coordinated). However when displaying the date and time to a user, it may not be appropriate to show the date and time in UTC. So, we have to convert it to the user’s time zone. Here we will see how to store data and time in UTC datetime format and convert it to different time zones when retrieving the stored date time.

Storing date and time in UTC date time

It is always a good practice to store the date-time in UTC. You can do this in a few different ways. One way is to get the UTC time directly and store it in a date-time datatype column. Next way is to convert the given local time to UTC time and then store it in a date time column. Another way is to use the datetimeoffset to add the offset to UTC and store it in a datetimeoffset column. Let’s see them one by one with examples.

1. Using GETUTCDATE()

Using GETUTCDATE() instead of GETDATE() will get you the current UTC date and time. So, wherever you want to get the current server date time during insert or update, use GETUTCDATE(). Here is an example:

/* Create a table */
CREATE TABLE [dbo].[MTB_TABLE_UTC](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Order] [nvarchar](20) NULL,
	[OrderedOn_LocalTime] [datetime] NULL,
	[OrderedOn_UTC] [datetime] NULL
) ON [PRIMARY]
GO

/* Insert a record */
INSERT INTO [dbo].[MTB_TABLE_UTC] 
	VALUES ('Bicycle', GETDATE(), GETUTCDATE())
GO

/* Fetch and see the inserted record */
SELECT * FROM [dbo].[MTB_TABLE_UTC]
GO

/*
RESULT:
ID          Order                OrderedOn_LocalTime     OrderedOn_UTC
----------- -------------------- ----------------------- -----------------------
1           Bicycle              2021-08-18 06:34:32.207 2021-08-18 10:34:32.207

(1 row affected)
*/
Using UTC DateTime - GETUTCDATE()

In the above example, I have created a table with one column to store the local server date time (which is Eastern Time US & Canada) using GETDATE() and another column to store the current UTC date and time using GETUTCDATE(). If you notice UTC time in column OrderedOn_UTC is 4 hours ahead of Eastern Time.

2. Convert DateTime to UTC

Sometimes, you may need to store the user entered date and time. The user may enter the date and time according to his or her local time. However, you may need to store it in UTC. In such a scenario, you can convert the user entered date and time to UTC using the DATEDIFF and DATEADD functions. Here is an example:

If a user enters a date time, you can find the difference between the current local and UTC time in seconds and add it to the user entered time.

Declare 
	@OrderDate AS DATETIME,
	@OrderDateUTC As DATETIME;

SET @OrderDate = '2021-08-15 18:32:15';
SET @OrderDateUTC = DATEADD(second, 
    DATEDIFF(second, GETDATE(), GETUTCDATE()), @OrderDate);

INSERT INTO [dbo].[MTB_TABLE_UTC] 
	VALUES ('Bike', @OrderDate, @OrderDateUTC)
GO

/* Fetch and see the inserted record */
SELECT * FROM [dbo].[MTB_TABLE_UTC] WHERE ID > 1
GO

/*
RESULT:
ID          Order                OrderedOn_LocalTime     OrderedOn_UTC
----------- -------------------- ----------------------- -----------------------
2           Bike                 2021-08-15 18:32:15.000 2021-08-15 22:32:15.000

(1 row affected)
*/
Using UTC DateTime - Convert to UTC

In the above example, the user entered time is “2021-08-15 18:32:15″. To make this and UTC time, I have got the difference between GETDATE() and GETUTCDATE() in seconds and then added it to the user entered time. Thus the entered time is converted to UTC as “2021-08-15 22:32:15″.

3. Using DATETIMEOFFSET

In the previous techniques we have used GETUTCDATE() to get the current UTC date-time and store it in a DATETIME column. Now we will see how to use SYSDATETIMEOFFSET() to get local date-time with offset to UTC and store it in the DATETIMEOFFSET column. Unlike GETUTCDATE(). SYSDATETIMEOFFSET() returns a datetimeoffset(7) in which the time zone offset is included in the system date and time. Let’s see an example:

/* Create a table with datetimeoffset(7) as one of the columns */
CREATE TABLE [dbo].[MTB_TABLE_UTC](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Order] [nvarchar](20) NULL,
	[OrderedOn_LocalTime] [datetime] NULL,
	[OrderedOn_DTO] [datetimeoffset](7) NULL
) ON [PRIMARY]
GO

/* Insert a record */
INSERT INTO [dbo].[MTB_TABLE_UTC] 
	VALUES ('Bicycle', GETDATE(), SYSDATETIMEOFFSET())
GO

/* Fetch and see the inserted record */
SELECT * FROM [dbo].[MTB_TABLE_UTC]
GO

/* 
RESULT:
ID          Order                OrderedOn_LocalTime     OrderedOn_DTO
----------- -------------------- ----------------------- ----------------------------------
1           Bicycle              2021-08-18 06:21:18.410 2021-08-18 06:21:18.4219099 -04:00

(1 row affected)
*/
Using UTC DateTime - DATETIMEOFFSET

Converting UTC datetime to a local time zone

Now we will see how to convert the date-time stored in UTC format to the local time zone. From SQL Server 2016 and higher versions, we have a couple of techniques for converting time zones. Let us see them one by one.

1. Convert UTC date-time using AT TIME ZONE

Using AT TIME ZONE is an easy way to convert UTC date-time or DATETIMEOFFSET to your desired time zone in datetimeoffset format. While using AT TIME ZONE, you have to specify the destination time zone in their respective names like Eastern Standard Time, Pacific Standard Time, etc… To get the respective time zone names, you can use the system T-SQL function sys.time_zone_info.

system T-SQL function sys.time_zone_info

Now, let us see how to use the AT TIME ZONE in a select query to fetch the UTC time and convert to different time zones.

/* Create a table and insert UTC time*/
CREATE TABLE [dbo].[MTB_TABLE_UTC](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Order] [nvarchar](20) NULL,
	[OrderedOn_LocalTime] [datetime] NULL,
	[OrderedOn_UTC] [datetime] NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[MTB_TABLE_UTC] 
	VALUES ('Bicycle', GETDATE(), GETUTCDATE())
GO

/* Now fetch and convert the UTC datetime to different time zones */
SELECT *,
	OrderedOn_UTC AT TIME ZONE 'Eastern Standard Time' AS 'EST'
	FROM [dbo].[MTB_TABLE_UTC];
SELECT *,
	OrderedOn_UTC AT TIME ZONE 'Central Standard Time' AS 'CST'
	FROM [dbo].[MTB_TABLE_UTC];
SELECT *,
	OrderedOn_UTC AT TIME ZONE 'Pacific Standard Time' AS 'PST'
	FROM [dbo].[MTB_TABLE_UTC];
GO
Using AT TIME ZONE

2. Using SWITCHOFFSET

You can use the SWITCHOFFSET function to convert a datetimeoffset data type from one time zone to another. Here is an example to convert datetimeoffset with ‘Eastern Standard Time’ to ‘Central Standard Time’

/* Create a table with datetimeoffset(7) as one of the columns */
CREATE TABLE [dbo].[MTB_TABLE_UTC](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Order] [nvarchar](20) NULL,
	[OrderedOn_LocalTime] [datetime] NULL,
	[OrderedOn_DTO] [datetimeoffset](7) NULL
) ON [PRIMARY]
GO

/* Insert a record */
INSERT INTO [dbo].[MTB_TABLE_UTC] 
	VALUES ('Bicycle', '2021-08-19 06:35:15.00', '2021-08-19 06:35:15.00 -04:00')
GO

/* Now convert the UTC datetime to different time zones */
SELECT *,
	SWITCHOFFSET(OrderedOn_DTO, '-05:00') AS 'CST'
	FROM [dbo].[MTB_TABLE_UTC];
GO
Using SWITCHOFFSET

Reference


Related Articles & Tools

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.