SQL: Setting local date and time for Azure SQL Database

I have previously posted information about how to get local date and time happening in Azure SQL Database. Many people have been frustrated about the inability to set any date and time to work with, rather than just UTC.

I posted here about calculating it, and here about how to set it for your session.

While the workarounds were helpful, there were many situations where they just weren’t enough. One simple example is where you’re offering a Software as a Service (SaaS) application and want each tenant database to have its own date and time configuration, but many other people just wanted their database to reflect their own local time.

So, it was great to see UC’s recent post about the new options for this!

You can now set this two ways:

Database Level

First, you can set it at the database level by using a database-scoped configuration. This will be the most common.

ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE = 'E. Australia Standard Time';

Now you might be wondering how you find the appropriate names for the time zones. That’s also easy. In my case, I’m looking for any that contain Australia.

SELECT *
FROM sys.time_zone_info
WHERE [name] LIKE '%Australia%';

You can also view the current value by querying the database scoped configurations:

SELECT *
FROM sys.database_scoped_configurations
WHERE name = 'TIME_ZONE';

There is also an option to reset it, by using the keyword LOCAL:

ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE = LOCAL;

Session Level

Now this one is interesting as well. You might just want to set the value for your session, instead of for the whole database. That’s the option that ANSI/ISO SQL suggests. And that’s now also possible:

SET TIME ZONE 'E. Australia Standard Time';

And you can query what it’s currently set to:

SELECT time_zone
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;

What’s missing?

It strikes me that I’d want to be able to set a session time zone based upon a query or a value from elsewhere. But this doesn’t work:

DECLARE @TimeZone sysname = 'E. Australia Standard Time';

SET TIME ZONE @TimeZone;

I’m not a fan of all these SQL Server commands that can’t take variables or parameters. You might wonder if you can set it with dynamic SQL, like this:

DECLARE @TimeZone sysname = N'New Zealand Standard Time';
DECLARE @SQL nvarchar(max);

SET @SQL = N'SET TIME ZONE ''' + @TimeZone + N''';';
EXEC (@SQL);

SELECT time_zone
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;