Introduction

I recently wrote a blog where I created a table containing the conversion of dates between time zones. I took this a little further and created a couple of functions that allows me to convert a value in real-time done to the millisecond.

This blog will not have as much information on the time zones. If you would like to learn more about the problem this is solving, please check out my recent post, Generating DST Conversion Table In SQL Server

The Scripts

For this particular implementation, the easiest way for me to accomplish this was to create two different scripts.

The first script is to get us a table of time zones we can use for our conversions.

-- ============================================================================================
-- Author: Kevin Williams ([email protected] or https://KevinOfTech.com)
-- Create date: 2022-07-16
-- Description:	
--	Returns a list of time zone conversions for a specified date. 
-- ============================================================================================
CREATE FUNCTION GetTimezones(@SourceDateTime DATETIME)	
RETURNS TABLE
AS
RETURN
(
	SELECT
		  [calendar_year]
		, [timezone]
		, [timezone_offset]
		, CASE 
			WHEN [calendar_year] < 1966 THEN NULL
			WHEN [calendar_year] >= 2007 THEN [start_date]
			ELSE DATEADD(WEEK, 3, [start_date])
			END AS [start_datetime]
		, CASE 
			WHEN [calendar_year] < 1966 THEN NULL
			WHEN [calendar_year] >= 2007 THEN [end_date]
			ELSE DATEADD(WEEK, -1, [end_date])
			END AS [end_datetime]
	FROM (
		SELECT 
			  [calendar_year]
			, tmz.timezone
			, tmz.timezone_offset
			, CASE DATEPART(dw, [start_week])
				WHEN 1 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 171, [start_week])
				WHEN 2 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 315, [start_week])
				WHEN 3 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 291, [start_week])
				WHEN 4 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 267, [start_week])
				WHEN 5 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 243, [start_week])
				WHEN 6 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 219, [start_week])
				WHEN 7 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 195, [start_week])
				END AS [start_date]
			, CASE DATEPART(dw, [end_week])
				WHEN 1 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 2, [end_week])
				WHEN 2 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 146, [end_week])
				WHEN 3 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 122, [end_week])
				WHEN 4 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 98, [end_week])
				WHEN 5 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 74, [end_week])
				WHEN 6 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 50, [end_week])
				WHEN 7 THEN DATEADD(hour, ABS(tmz.timezone_offset) + 26, [end_week])
				END AS [end_date]
		FROM (
			SELECT
					YEAR(@SourceDateTime) AS [calendar_year]
				, CONCAT(YEAR(@SourceDateTime), '/03/01') AS [start_week]
				, CONCAT(YEAR(@SourceDateTime), '/11/01') AS [end_week]
			) y
			CROSS APPLY (
				SELECT
					CASE	
						WHEN [timezone_offset] = 0 THEN 'Universal'
						WHEN [timezone_offset] = 4 THEN 'Eastern'
						WHEN [timezone_offset] = 5 THEN 'Central'
						WHEN [timezone_offset] = 6 THEN 'Mountain'
						WHEN [timezone_offset] = 7 THEN 'Pacific'
					END AS [timezone]
					, [timezone_offset] * -1 AS [timezone_offset]
				FROM (
					SELECT 
						[time_zone] AS [timezone_offset]
					FROM (
						SELECT TOP 8 [time_zone] = ROW_NUMBER() OVER (ORDER BY s1.[object_id]) - 1
						FROM sys.all_objects AS s1
							CROSS JOIN sys.all_objects AS s2
						ORDER BY s1.[object_id]
					) tmz
				) tmz
			) tmz
		) dst
);

The second script converts the actual variable to the new time zone that is specified.

-- ============================================================================================
-- Author: Kevin Williams ([email protected] or https://KevinOfTech.com)
-- Create date: 2022-07-16
-- Description:	
--	Converts a specified datetime to a different time zone.
-- 
-- Example:
-- SELECT dbo.ConvertToTimezone(@SourceDateTime, @SourceStandardOffset, @targetStandardOffset);
-- ============================================================================================
CREATE FUNCTION ConvertToTimezone(@SourceDateTime DATETIME, @SourceStandardOffset SMALLINT, @targetStandardOffset SMALLINT)	
RETURNS DATETIME
WITH EXECUTE AS OWNER
AS
BEGIN
	DECLARE @ReturnDateTime DATETIME;

	SET @ReturnDateTime = (
		SELECT 
			[ConvertedDateTime]
		FROM (
			SELECT
				  @SourceDateTime AS [SourceDateTime]
				, @SourceStandardOffset AS [SourceStandardOffset]
				, @targetStandardOffset AS [targetStandardOffset]
				, [UTC]
				, DATEADD(hh, 
					 CASE	
						WHEN YEAR(UTC) < 1966 THEN NULL
						WHEN UTC < t.[start_datetime] OR UTC >= t.[end_datetime] THEN t.[timezone_offset] - 1
						ELSE t.[timezone_offset]
						END, 
						UTC) AS [ConvertedDateTime]
				,  
					 CASE	
						WHEN YEAR(UTC) < 1966 THEN NULL
						WHEN UTC < t.[start_datetime] OR UTC >= t.[end_datetime] THEN t.[timezone_offset] - 1
						ELSE t.[timezone_offset]
						END
						AS [ConvertedDateTime_offset]
			FROM (
				SELECT DISTINCT
					 DATEADD(hh, 
					 CASE	
						WHEN YEAR(@SourceDateTime) < 1966 THEN s.[timezone_offset]
						WHEN CONVERT(TIME, @SourceDateTime) < '02:00' AND CONVERT(DATE, @SourceDateTime) <= CONVERT(DATE, s.[start_datetime]) THEN s.[timezone_offset] - 1
						WHEN CONVERT(TIME, @SourceDateTime) >= '02:00' AND CONVERT(DATE, @SourceDateTime) >= CONVERT(DATE, s.[end_datetime]) THEN s.[timezone_offset] - 1
						ELSE s.[timezone_offset]
						END * - 1,
						@SourceDateTime) AS [UTC]
				FROM dbo.GetTimezones(@SourceDateTime) s
				WHERE s.timezone_offset = @SourceStandardOffset
			) a
				LEFT OUTER JOIN dbo.GetTimezones(@SourceDateTime) t ON t.timezone_offset = @targetStandardOffset
		) a
	);

	RETURN @ReturnDateTime;
END

Here is an example of how it can be called.

-- Setup our variables
DECLARE @SourceDateTime DATETIME = '2022-03-11 01:15:12:100'; 
DECLARE @SourceStandardOffset SMALLINT = -7; -- Converting from Pacific Time Zone
DECLARE @targetStandardOffset SMALLINT = -5; -- Converting to Central Time Zone

-- Call the function
SELECT dbo.ConvertToTimezone(@SourceDateTime, @SourceStandardOffset, @targetStandardOffset)  AS [foo];

Conclusion

I hope this is useful to someone. Feel free to use it anywhere. I would appreciate my information being left in the script for credit and in case anyone wants to contact me with updates they made on the script. Please feel free to let me know if you find any bugs in the script.

I don't have a comments section yet, so feel free to send me feedback on this blog.