How to Get Correct Datetimes with Azure SQL

Originally published at https://fek.io.

I recently ran into an issue with trying to return the correct datetime for comparison using Azure SQL Database. I have used Microsoft SQL Server for most of my career, but until this year I had never used Azure SQL Database.

Azure SQL Database has a lot of similarities with the on premise version of SQL Server, but since this service is hosted on Azure there are some differences. The issue I ran across recently had to do with the GETDATE() and GETUTCDATE() functions.

In the Azure version of SQL both functions will return the same value, which is UTC time. The time zone I am working in is the US East coast time zone, which runs five hours behind UTC time, and four hours during daylight saving time.

I needed to be able to return records for upcoming events that will occur after the current time, but I was having to apply a DATEADD() function to get the correct time. I had this query in a stored procedure, so I needed have this work no matter if it was daylight saving time or not.

It turns out that SQL has a nice feature for getting the datetime for the correct time zone.

Let us say we have a query that returns events from a table called MyEvents, and there is a column for storing the ScheduledEvent as a…