Solving Azure SQL Database timezone problems using AT TIME ZONE

When moving a database to Azure SQL Databases one of the things that must be addressed is timezones.  Azure PaaS databases always use UTC as the time zone so that a service can be moved between data centers in different time zones without a change in functionality.

The scenario here is that I have a custom built small database that I want to move from on-premise to Azure SQL Database.  I operate out of only one time zone, Eastern Standard Time (EST), so all datetime columns have dates and times that reflect EST.  After moving the database to Azure, I notice that the inserted times are UTC, not EST:

After Insert

The first thing that must be done prior to loading any new data is updating all current date times to UTC.  This can be accomplished using a new feature of SQL 2016 and Azure: AT TIME ZONE.  Run a command similar to the one below on your datetime columns and the info will now be stored based on the UTC time.

UPDATE dbo.Orders SET DateOrdered=CONVERT(DATETIME,DateOrdered AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'UTC')

Before and After Update

Now you can migrate your database to azure and insert new records using UTC time:

INSERT INTO dbo.Orders (DateOrdered,Product) VALUES (GETDATE(),'Laptop')

Once that is done, you can return the UTC times in EST by using AT TIME ZONE again:

SELECT 
DateOrdered, 
CONVERT(DATETIME,DateOrdered AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') as 'DateOrderedEST',
Product 
FROM dbo.Orders

Results showing UTC and EST

Using AT DATE TIME is a great tool to help us solve time zone issues and it is a small part of the challenges of moving to Azure.  

 

Some useful links/references: