Converting from UTC to Central Daylight Time (or some other DST time zone)

You might think conversion is simple.  Even that there is already a function to do it.  The fact is, it’s not easy at all and it’s not really practical for OS providers to create such a function either.  A generalized solution is so complex that companies exist just to create and maintain class libraries to deal with the complexities.  The rules change not only depending on where you are in the world but what year it is (and more).  This example is specialized for the Central Time Zone and uses rules specific to the United States but it is adaptable to other similarly narrow situations.  Under these constraints the algorithm performs better than any other I’ve seen.

The function can be used like this:

SELECT u.Utc, c.CentralTime
FROM MyTable u
CROSS APPLY UtcToCentralTime(u.Utc) c

Or like this:

SELECT u.Utc, (SELECT CentralTime FROM UtcToCentralTime(u.Utc)) CentralTime
FROM MyTable u

Either usage method might perform better than the other under your specific query conditions. Try them both if you need the ‘best’ performance. I’ve not yet seen a case when the difference was significant though.

 -- =============================================
 -- Author: Rick Bielawski
 -- Description: Converts a GMT(UTC) timestamp to Central Time
 -- The rules for dates between Apr 2000 and Oct 2027 are currently
 -- coded.  Dates outside that range may or may-not return the correct
 -- results.  No warning is given for out of range values.  And, if the 
 -- laws change, even dates before 2027 may not be correct until this
 -- function is updated.
 -- =============================================
 CREATE FUNCTION UtcToCentralTime (@UTC DATETIME2(7))
 RETURNS TABLE
 AS
 RETURN
 WITH UtcDstTime(StartTime, EndTime)
 AS (
 -- There is a smaller amount of time during the year when offset is -6 vs -5 (34 weeks vs 18) so
 -- I made the default -5 so that times the table doesn't cover are more likely to still be correct.
 -- The table therefore needs to contain non-DST ranges rather than the DST time range.
 -- Notice too that the time in these ranges must be expressed as UTC(GMT)
 SELECT CAST('20001029 7:00' AS DATETIME), CAST('20010401 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20011028 7:00' AS DATETIME), CAST('20020407 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20021027 7:00' AS DATETIME), CAST('20030406 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20031026 7:00' AS DATETIME), CAST('20040404 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20041031 7:00' AS DATETIME), CAST('20050403 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20051031 7:00' AS DATETIME), CAST('20060402 8:00' AS DATETIME) UNION ALL
 -- DST changed dates in 2007
 SELECT CAST('20061029 7:00' AS DATETIME), CAST('20070311 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20071104 7:00' AS DATETIME), CAST('20080309 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20081102 7:00' AS DATETIME), CAST('20090308 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20091101 7:00' AS DATETIME), CAST('20100314 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20101107 7:00' AS DATETIME), CAST('20110313 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20111106 7:00' AS DATETIME), CAST('20120311 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20121104 7:00' AS DATETIME), CAST('20130310 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20131103 7:00' AS DATETIME), CAST('20140309 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20141102 7:00' AS DATETIME), CAST('20150308 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20151101 7:00' AS DATETIME), CAST('20160313 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20161106 7:00' AS DATETIME), CAST('20170312 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20171105 7:00' AS DATETIME), CAST('20180311 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20181104 7:00' AS DATETIME), CAST('20190310 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20191103 7:00' AS DATETIME), CAST('20200308 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20201101 7:00' AS DATETIME), CAST('20210314 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20211107 7:00' AS DATETIME), CAST('20220313 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20221106 7:00' AS DATETIME), CAST('20230312 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20231105 7:00' AS DATETIME), CAST('20240310 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20241103 7:00' AS DATETIME), CAST('20250309 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20251102 7:00' AS DATETIME), CAST('20260308 8:00' AS DATETIME) UNION ALL
 SELECT CAST('20261101 7:00' AS DATETIME), CAST('20270314 8:00' AS DATETIME)
 )
 SELECT   CASE WHEN d.StartTime IS NULL THEN DATEADD(HOUR, -5, UTC)
 ELSE DATEADD(HOUR, -6, UTC)
 END AS CentralTime
 FROM     (SELECT @UTC AS UTC
 ) u
 LEFT JOIN UtcDstTime d
 ON u.UTC >= d.StartTime
 AND u.UTC < d.EndTime;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: