SQL: Getting local date and time in Azure SQL Database

I work a lot with Azure SQL Database, and if you’ve done that, you will have realised that, just like other Azure services, the time zone is set to UTC. Select from GETDATE() or SYSDATETIME(), and you’ll find it’s the current UTC date and time i.e. the same as you’d get from the SYSUTCDATETIME() function.

I can see why that makes sense much of the time. If your users are all over the world, that seems an entirely appropriate setting. Same deal if you’re linking different systems together: it’s good to have a common timeframe.

However, if all your users are in Sydney, suddenly that seems to be a problem.

I wish there was a way to set the timezone for an Azure SQL Database but currently, there isn’t.

sys.time_zone_info

In SQL Server 2016, we saw Microsoft add a new system view.  It’s called sys.time_zone_info. You can read about it here. If you query it, you can see what it does:

SELECT * FROM sys.time_zone_info;

The output is shown here:

It has time zones and their current UTC offset, and also lets you know if the time zone currently has daylight saving time.

Note that this isn’t giving you historical info. That’s a much harder problem. It’s just the value at the current time.

Current Local Time

Now in theory, we could use this to get the current local time for a given timezone:

USE tempdb;
GO

CREATE OR ALTER FUNCTION dbo.CurrentLocalTime
( 
    @TimeZoneName sysname
)
RETURNS datetime2
AS
BEGIN

--
-- Test examples: 
/*

SELECT dbo.CurrentLocalTime('AUS Eastern Standard Time');

*/
 
  RETURN DATEADD(hour, 
                 TRY_CAST((SELECT REPLACE(current_utc_offset, N':', N'.')
                           FROM sys.time_zone_info
                           WHERE [name] = @TimeZoneName) AS decimal(18,2)), 
                 SYSDATETIME());
END;
GO

The challenge with this, is it’s way, way too slow. I’ve also found it leads to really poor estimates in query plans. Now while the SQL Server team has done great work with T-SQL scalar functions lately, this function would still have performance issues, and restrict parallelism. Based on that, I wouldn’t recommend calling it for every row in a large rowset.

AT TIME ZONE

So let’s look at a faster option. If you are comfortable with including a larger (and less obvious) expression directly into your code in place of GETDATE() or SYSDATETIME(), you can create a better performing expression by using the AT TIME ZONE modifier that was added in SQL Server 2016. It’s documented here.

You can use SYSDATETIMEOFFSET() to retrieve the current date, time, and offset at the server, and then use AT TIME ZONE to change the value to the target time zone. Then, because you no doubt want a date, a datetime or datetime2 value (rather than a datetimeoffset), you’ll need to CAST it to what you need.

For example, if you want the current date and time as a datetime value like GETDATE() does, do this:

CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'AUS Eastern Standard Time' AS datetime)

If you want the current date and time as a datetime2 value like SYSDATETIME() does, do this:

CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'AUS Eastern Standard Time' AS datetime2)

And if you just want the current date, you can do this:

CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'AUS Eastern Standard Time' AS date)

I know it’s wordier and far less obvious to someone reading the code than the function, but it will perform better. So if performance matters, this will be a better option. I hope that helps you to get around this issue.

2020-03-12