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)
*/
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)
*/
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)
*/
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.
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
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
Reference
- Learn more about DATETIMEOFFSET at Microsoft Docs.
- Read more about AT TIME ZONE at Microsoft Docs.
- More about SWITCHOFFSET at Microsoft Docs.