Manual UTC Conversion

During a project last year I was creating a FACT table from one of many databases in my organization used to track program information. Using SQL Server, one of the fields that I wanted to pull was Air Date/Time of a program. When I first created my fact table, I noticed that the Air Date/Time field was in UTC. I suspect this database was created with a time OFFSET field but I didn’t have schema documentation for it.

I decided create my own conversion calculation to East Coast time. I’ve been working with SQL for a little time now but I’m no expert. So the challenge I faced was adjusting for Standard versus Day Light Savings because UTC does not change. I looked up the dates when we adjust our clocks from 2011 – 2025. The rest was the following SQL CASE statement:

CASE
WHEN [Table].[Date Field] BETWEEN ‘2011-03-13 07:00:00:000’ AND ‘2011-11-06 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2012-03-11 07:00:00:000’ AND ‘2012-11-04 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2013-03-10 07:00:00:000’ AND ‘2013-11-03 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2014-03-09 07:00:00:000’ AND ‘2014-11-02 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2015-03-08 07:00:00:000’ AND ‘2015-11-01 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2016-03-13 07:00:00:000’ AND ‘2016-11-06 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2017-03-12 07:00:00:000’ AND ‘2017-11-05 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2018-03-11 07:00:00:000’ AND ‘2018-11-04 06:00:00:000 THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2019-03-10 07:00:00:000’ AND ‘2019-11-03 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2020-03-08 07:00:00:000’ AND ‘2020-11-01 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2021-03-14 07:00:00:000’ AND ‘2021-11-07 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2022-03-13 07:00:00:000’ AND ‘2022-11-06 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2023-03-12 07:00:00:000’ AND ‘2023-11-05 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2024-03-10 07:00:00:000’ AND ‘2024-11-03 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
WHEN [Table].[Date Field] BETWEEN ‘2025-03-09 07:00:00:000’ AND ‘2025-11-02 06:00:00:000’ THEN DATEADD(hour,-4,[Table].[Date Field])
ELSE DATEADD(hour,-5,[Table].[Date Field]) END AS [Date Name]

I first created a parameter in Tableau to let the user decide to view the data between standard and daylight savings. But I wanted the users to avoid any confusion between the viz and any other area where one can see program air date/time. Secondly, for Tableau best data practices, do heavy data lifting calculations before connection to Tableau. Software like SQL Server are built for this type of data manipulation. Especially when working with millions of records, long winded calculations can slow Tableau performance.

Thanks to my manager for encouraging this post. I hope it saves anyone time who needs to convert UTC date fields. 

UPDATE: To perform the same in Tableau click here.

Share Button

2 comments

  1. Oops! I was looking through this to use somewhere else, and found only one error:
    2017-11-02 06:00:00:000 should be changed to 2017-11-05 06:00:00:000

    Could someone please verify that the above is correct? I’ve looked at so many dates lately, I could be flat out crazy.

Leave a Reply