Migrate datetime data to datetimeoffset with AT TIME ZONE

I recently reviewed, worked on, and added a similar example to the DATETIMEOFFSET Microsoft Learn Docs article at the recommendation of my colleague Randolph West, who guessed (accurately) I would enjoy such a task. It was a nice pre-Build diversion. 

This topic is one that I have co-presented on in the past and hounded project capstone review presentations about. If you're not storing time zone offset in your date/time data, you're setting yourself up for future pain. That future pain is not what this blog post is about.

One of the big complications of converting date/time data without time zone info is Daylight Savings Time. There are various versions of time zone weirdness around the globe, but in most of the United States, we fall backwards and spring forwards by one hour each year. (This is why the state of Indiana has its own time zone, my dear friend and editor Drew Lanclos noted.)

Simply subtracting the current offset hours to arrive at Eastern/Central/Mountain/Pacific Standard Time will be incorrect for half of each year. This is most invisibly painful when applied to historic data.

How NOT to convert data to datetimeoffset

DECLARE @TimeZone VARCHAR(50);

SELECT @TimeZone = [name]

FROM sys.time_zone_info

WHERE [name] = 'Pacific Standard Time';

--Pretend that audit_created below is a UTC date that needs -- to be converted to the local timezone for display.

--If executed between Nov-March, 'Bad Strategy' below is wrong for historical dates between March-Nov.

--Between March-Nov, 'Bad Strategy' below is wrong for historical dates between Nov-March.

SELECT

    UTCDate = audit_created  

,   BadStrategy = DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), audit_created ) --Don't use!

FROM #audit_created

GO

How to convert data to datetimeoffset

Use the AT TIME ZONE syntax, introduced in SQL Server 2016.

DECLARE @TimeZone VARCHAR(50);

SELECT @TimeZone = [name]

FROM sys.time_zone_info

WHERE [name] = 'Pacific Standard Time';

--Pretend that audit_created below is a UTC date that needs to be converted to the local timezone for display.

SELECT

    UTCDate = audit_created  

,   CorrectStrategy = audit_created  AT TIME ZONE 'UTC' AT TIME ZONE @TimeZone --SQL 2016+ only

FROM #audit_created

GO

In the SELECT statement, the AT TIME ZONE syntax first adds UTC time zone offset to the existing audit_created column data, then converts the data from UTC to Pacific Standard Time, correctly adjusting the historical data for each prior Daylight Savings Time range in the United States.

A longer sample lab for you to try it yourself

DROP TABLE IF EXISTS dbo.audit

GO

CREATE TABLE dbo.audit

(audit_created datetime2(0) primary key)

GO

INSERT INTO dbo.audit (audit_created) VALUES ('1/1/2024 12:00:00');

INSERT INTO dbo.audit (audit_created) VALUES ('5/1/2024 12:00:00');

INSERT INTO dbo.audit (audit_created) VALUES ('12/1/2024 12:00:00');

GO

ALTER TABLE dbo.audit

ADD audit_created_offset datetimeoffset(0) NULL

GO

DECLARE @TimeZone VARCHAR(50);

SELECT @TimeZone = [name]

FROM sys.time_zone_info

WHERE [name] = 'Pacific Standard Time';

UPDATE dbo.audit

SET audit_created_offset = audit_created AT TIME ZONE 'UTC' AT TIME ZONE @TimeZone

WHERE audit_created_offset is NULL

GO

SELECT * FROM dbo.audit

audit_created audit_created_offset 2024-01-01 12:00:00 2024-01-01 04:00:00 -08:00 2024-05-01 12:00:00 2024-05-01 05:00:00 -07:00 2024-12-01 12:00:00 2024-12-01 04:00:00 -08:00

Note how the January and December dates were shifted to UTC -08:00, but the May date was shifted to UTC -07:00.

Pre-2016 lab

Pre-2016 was infinitely more pleasant than the present day in many ways. Time zone conversion is not one of them.

Trying to do this while working in a version of SQL Server prior to 2016? My condolences, but here's the solution I worked out a five years ago when I was in the same situation, and could not use AT TIME ZONE.

--This lab demonstrates a common antipattern for converting UTC to the local timezone.

--Will work <SQL 2016. Optional code at end to uncomment if SQL 2016+

--Create the DST population table in comment below at bottom. This could be useful for your apps pre-SQL2016.

declare @audit_created table

(audit_created datetime2(0))

insert into @audit_created (audit_created)

values

 ('3/12/2017 03:00')

,('3/12/2017 04:00')

,('3/12/2017 05:00') --This will be wrong in the the Incorrect pre2016 method if we are currently in DST (March-Nov)

,('3/12/2017 06:00')

,('3/12/2017 07:00')

,('3/12/2017 08:00')

,('3/12/2017 09:00')

,('11/5/2017 03:00')

,('11/5/2017 04:00')

,('11/5/2017 05:00') --This will be wrong in the the Incorrect pre2016 method if we are currently not in DST (Nov-March)

,('11/5/2017 06:00')

,('11/5/2017 07:00')

,('11/5/2017 08:00')

,('11/5/2017 09:00')

,('1/1/2017 05:00') --One of these two rows will be wrong for Central US Time in the Incorrect pre2016 method. It'll be the one that doesn't match our CURRENT DST setting.

,('6/1/2017 05:00') --One of these two rows will be wrong for Central US Time in the Incorrect pre2016 method. It'll be the one that doesn't match our CURRENT DST setting.

select          

    audit_created  

,   audit_created_actually_at_UTC               =   TODATETIMEOFFSET(audit_created, 0)

,   Incorrect_pre2016_method_Central_time       =   DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), audit_created )

,   Incorrect_pre2016_method_Central_time_date  =   CONVERT(date, DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), audit_created ))

,   Correct_pre2016_method_Central_time         =  

    SWITCHOFFSET(TODATETIMEOFFSET(audit_created, 0),  CASE WHEN EXISTS (select * from dbo.DSTDates where BeginDate<=TODATETIMEOFFSET(audit_created, 0) and EndDate>TODATETIMEOFFSET(audit_created, 0))

                                                            THEN -300 -- -5:00, CDT

                                                            ELSE -360 -- -6:00, CST

                                                            END)

,   Correct_pre2016_method_Central_time_date    =   CONVERT(DATE,

    SWITCHOFFSET(TODATETIMEOFFSET(audit_created, 0),  CASE WHEN EXISTS (select * from dbo.DSTDates where BeginDate<=TODATETIMEOFFSET(audit_created, 0) and EndDate>TODATETIMEOFFSET(audit_created, 0))

                                                            THEN -300 -- -5:00, CDT

                                                            ELSE -360 -- -6:00, CST

                                                            END)

    )

--Uncomment the following two rows for the right way to do this in SQL 2016+

--,    Correct_2016_method              =   audit_created  AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time'

--,    Correct_2016_method__date        =   convert(date,  (audit_created AT TIME ZONE 'UTC'  AT TIME ZONE 'Central Standard Time'))

from @audit_created as A

/*

--Here's a table of DST dates since 1970 to share at parties and impress your friends.

DROP TABLE dbo.DSTDates

GO

 CREATE TABLE dbo.DSTDates

 (  BeginDate datetimeoffset(0)

 ,  EndDate datetimeoffset(0)

 )

 GO

 CREATE CLUSTERED INDEX IDX_CL_DSTDates on dbo.DSTDates (BeginDate, EndDate)

 GO

  INSERT INTO dbo.DSTDates (BeginDate, EndDate)

VALUES

 ('4/26/1970 02:00 -06:00','10/25/1970 02:00 -05:00'),

('4/25/1971 02:00 -06:00','10/31/1971 02:00 -05:00'),

('4/30/1972 02:00 -06:00','10/29/1972 02:00 -05:00'),

('4/29/1973 02:00 -06:00','10/28/1973 02:00 -05:00'),

('1/6/1974 02:00 -06:00','10/27/1974 02:00 -05:00'),

('2/23/1975 02:00 -06:00','10/26/1975 02:00 -05:00'),

('4/25/1976 02:00 -06:00','10/31/1976 02:00 -05:00'),

('4/24/1977 02:00 -06:00','10/30/1977 02:00 -05:00'),

('4/30/1978 02:00 -06:00','10/29/1978 02:00 -05:00'),

('4/29/1979 02:00 -06:00','10/28/1979 02:00 -05:00'),

('4/27/1980 02:00 -06:00','10/26/1980 02:00 -05:00'),

('4/26/1981 02:00 -06:00','10/25/1981 02:00 -05:00'),

('4/25/1982 02:00 -06:00','10/31/1982 02:00 -05:00'),

('4/24/1983 02:00 -06:00','10/30/1983 02:00 -05:00'),

('4/29/1984 02:00 -06:00','10/28/1984 02:00 -05:00'),

('4/28/1985 02:00 -06:00','10/27/1985 02:00 -05:00'),

('4/27/1986 02:00 -06:00','10/26/1986 02:00 -05:00'),

('4/5/1987 02:00 -06:00','10/25/1987 02:00 -05:00'),

('4/3/1988 02:00 -06:00','10/30/1988 02:00 -05:00'),

('4/2/1989 02:00 -06:00','10/29/1989 02:00 -05:00'),

('4/1/1990 02:00 -06:00','10/28/1990 02:00 -05:00'),

('4/7/1991 02:00 -06:00','10/27/1991 02:00 -05:00'),

('4/5/1992 02:00 -06:00','10/25/1992 02:00 -05:00'),

('4/4/1993 02:00 -06:00','10/31/1993 02:00 -05:00'),

('4/3/1994 02:00 -06:00','10/30/1994 02:00 -05:00'),

('4/2/1995 02:00 -06:00','10/29/1995 02:00 -05:00'),

('4/7/1996 02:00 -06:00','10/27/1996 02:00 -05:00'),

('4/6/1997 02:00 -06:00','10/26/1997 02:00 -05:00'),

('4/5/1998 02:00 -06:00','10/25/1998 02:00 -05:00'),

('4/4/1999 02:00 -06:00','10/31/1999 02:00 -05:00'),

('4/2/2000 02:00 -06:00','10/29/2000 02:00 -05:00'),

('4/1/2001 02:00 -06:00','10/28/2001 02:00 -05:00'),

('4/7/2002 02:00 -06:00','10/27/2002 02:00 -05:00'),

('4/6/2003 02:00 -06:00','10/26/2003 02:00 -05:00'),

('4/4/2004 02:00 -06:00','10/31/2004 02:00 -05:00'),

('4/3/2005 02:00 -06:00','10/30/2005 02:00 -05:00'),

('4/2/2006 02:00 -06:00','10/29/2006 02:00 -05:00'),

('3/11/2007 02:00 -06:00','11/4/2007 02:00 -05:00'),

('3/9/2008 02:00 -06:00','11/2/2008 02:00 -05:00'),

('3/8/2009 02:00 -06:00','11/1/2009 02:00 -05:00'),

('3/14/2010 02:00 -06:00','11/7/2010 02:00 -05:00'),

('3/13/2011 02:00 -06:00','11/6/2011 02:00 -05:00'),

('3/11/2012 02:00 -06:00','11/4/2012 02:00 -05:00'),

('3/10/2013 02:00 -06:00','11/3/2013 02:00 -05:00'),

('3/9/2014 02:00 -06:00','11/2/2014 02:00 -05:00'),

('3/8/2015 02:00 -06:00','11/1/2015 02:00 -05:00'),

('3/13/2016 02:00 -06:00','11/6/2016 02:00 -05:00'),

('3/12/2017 02:00 -06:00','11/5/2017 02:00 -05:00'),

('3/11/2018 02:00 -06:00','11/4/2018 02:00 -05:00'),

('3/10/2019 02:00 -06:00','11/3/2019 02:00 -05:00'),

('3/8/2020 02:00 -06:00','11/1/2020 02:00 -05:00'),

('3/14/2021 02:00 -06:00','11/7/2021 02:00 -05:00'),

('3/13/2022 02:00 -06:00','11/6/2022 02:00 -05:00'),

('3/12/2023 02:00 -06:00','11/5/2023 02:00 -05:00'),

('3/10/2024 02:00 -06:00','11/3/2024 02:00 -05:00'),

('3/9/2025 02:00 -06:00','11/2/2025 02:00 -05:00'),

('3/8/2026 02:00 -06:00','11/1/2026 02:00 -05:00'),

('3/14/2027 02:00 -06:00','11/7/2027 02:00 -05:00'),

('3/12/2028 02:00 -06:00','11/5/2028 02:00 -05:00'),

('3/11/2029 02:00 -06:00','11/4/2029 02:00 -05:00'),

('3/10/2030 02:00 -06:00','11/3/2030 02:00 -05:00'),

('3/9/2031 02:00 -06:00','11/2/2031 02:00 -05:00'),

('3/14/2032 02:00 -06:00','11/7/2032 02:00 -05:00')

*/

Yes, I know this blog post could have been written in 2016.

Tips, ideas, and recommendations on your technical blog

This technical blog is hardly the ideal, but I'll use this space here to summarize some of the guidance that my colleague Randolph West and I presented at a Microsoft MVP PGI this week. Their summary and mine specifically on the technical blogging are hopefully a resource for you, especially if you are early in your career or a Microsoft Student Ambassador.

Technical blogging can grow your technical depth and writing skillsets simultaneously. Ideas, tips, and recommendations:

Blog content ideas:

• Reference, samples, labs, especially for newcomers to the field.
• Deep dives into a topic you're interested in.
• Summaries and use cases of new features or changes.
• What's new? pages in Microsoft Docs are rich with blog ideas
• Checklists, best practices and lessons learned are great blog content.
• Your clients and customers are a fertile farm of reusable scripts, patterns and antipatterns, tools, and blog posts. "It depends" answers are great blog posts.
•  Technically reusable content from client to client is a value add. A public bucket or toolbox of lessons learned is valuable.
•  Remember the best way to learn a topic is to try and teach it (or explain it in a public blog).

Tips on blogging:

• You don't have to be unique (but don't plagiarize). You can write about any topic, even if it's been covered by bigger names. Your voice is valuable.
• Don't steal content or plagiarize, but you can admire and emulate (and attribute with links) the style/format of another author or blogger. Emulate things you like about someone else's process or research style or content format.
• Make it into a story if you can, "It tried this, it broke, I tried this, it didn't work, I fixed it this way..."
• A problem with no solution is worth blogging about. Sometimes, blogging about a problem is a great way to work the problem, and figure it out in the process.
• Write regularly, set a schedule. Pick a topic. Not every post has to be a novel.
• Do you ever write too much? Blogging can be great practice in distilling the core problem/concept to a palatable, communicable summary. It's easy to be wordy and redundant. It's a skill to practice writing more concise technical summaries. "If I Had More Time, I Would Have Written Less"
• Challenge your preconceived notions. Be humble in defeat and write about it. If you're proven wrong, your immediate reaction is usually to be defensive. The second reaction should be to learn from it, perhaps blog about it.

Get an editor, or volunteer to edit for blogs, newsletters, articles.

• Ask for a volunteer (or pay a) technical editor for your own blog.
• Listen to feedback. You trusted someone to edit you for a reason.
• Edits can feel like a gut punch. Don't take it personally.
• Politely ask to be someone else's volunteer technical editor for their blog.
• Provide constructive feedback, challenge assumptions, test technical scripts.
• Easier to find inconsistencies or gaps in someone else's work, it can be instructive to your own work.

Updated training list for a new DBA

Met an eager young volunteer at SQLSatBR over the weekend who inspired me to update a blog post that hadn't been refreshed in a few years. See the [UPDATED] Training To-Do List for New DBA.