Frequently we get requirements for UTC times in Azure SQL databases to be converted into various world time zones. Whilst scripting within the table builds and adding columns, or relying on sever settings, can produce this outcome there is a more flexible approach using the AT TIME ZONE (T-SQL) statement. Changes in Time Zone are automatically catered for throughout the year, dependent upon the date.
Using that command within statements provides a simple set of results that can written into queries as below. In this instance it is UTC converted to cater for Daylight Saving in the UK
This will produce results that provide the additional column, however additional columns add to storage and table sizes. We therefore took the approach of creating a SQL function that can be utilised in any query or view to create a more agile use of the command.
Creating a function as the script below
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[TimeZoneUK] (@UTCDate AS DATETIMEOFFSET(3))
RETURNS DATETIMEOFFSET(3)
AS
/*
Description: Add UK Time Zone and Daylight Savings to UTC Datetime
*/
BEGIN
RETURN @UTCDate AT TIME ZONE 'UTC' AT TIME ZONE 'GMT Standard Time'
END
GO
Enabling us to utilise that function in SQL scripts and views without the need to repeat the AT TIME ZONE commands on each line by placing the function in from of the UTC DateTime column already in the database as below.
SELECT
CONVERT(INT,CONVERT(CHAR(10),(CAST(dbo.TimeZoneUK(TimestampCreated) as date)), 112) ) as [Date Key]
,DATEPART(HOUR,dbo.TimeZoneUK(TimestampCreated)) AS [Hour Key]
,CAST(dbo.TimeZoneUK(TimestampCreated) AS DATE) AS [Event Date]
FROM [YourSchema].[YourTable]
With the results converted to UK DST within the appropriate dates.
Further details on the AT TIME ZONE command can be found within Microsoft's documentation here.
https://docs.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-2017
