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:
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')
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
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: