Converting Azure UTC DateTime to User Defined Locale and DateTime

Recently I had to write stored procedures to query records in an Azure SQL database.

Each record contained a DateTime field that that lets Azure Web Jobs know if a record should be processed.

We’ll call this field DateToRun.

Problem

When running queries for these records, DateToRun was compared against the SQL command GETDATE() in the WHERE clause to determine if a record should be processed.

The problem with this however was that each user in the system can set their own timezone in the application.

This meant the WHERE clause would often fail as their timezone wasn’t aligned with the locale in Azure SQL.

The Solution

The solution for this is to use SWITCHOFFSET and TZOFFSET, passing in the users timezone setting, and applying this to GETDATE().

For example:

Using this applies the correct datetime offset to the DateToRun field.

This meant the WHERE clause would evaluate correctly and process records at the time aligned with the users timezone settings.

Example

Here we have a sample query:

And the results:

The output UserDateTime_GMT field can then be used by other business areas or logic in the application

Resources

Learn and read more about SWITCHOFFSET here and TZOFFSET here.