sqlsql-servert-sqltimezoneazure-sql-managed-instance

How to make SQL Server use the Olson time zones instead of Windows ones by default

I'm trying to make my SQL Server use the Olson time zones instead of the Windows ones.

The web doesn't really give an answer on how to do it and make it system wide.

I don't know if that's possible to make it the default, and how to do it, or if I just should move to a different database provider but since my current infrastructure uses SQL Server I'd like to know if it is possible nonetheless.

If it's possible, I'd like to know how to do it locally but also when I'll be migrating it to an Azure SQL Server instance.

Solution

  • You can create a mapping table of Olson time zones to Windows time zones, and then use a function to convert.

    For example

    CREATE TABLE TzToWindows(Olson varchar(300) PRIMARY KEY, Windows varchar(50) NOT NULL);

    Then create an inline Table Valued Function to query it

    CREATE FUNCTION dbo.AtOlsonTimeZone (@value datetimeoffset, @olson varchar(300))
RETURNS TABLE
AS RETURN
SELECT @value AT TIME ZONE tz.Windows AS value
FROM TzToWindows tz
WHERE tz.Olson = @olson;

    You can then do a simple SELECT, or you can CROSS APPLY it from another table.

    SELECT *
FROM dbo.AtOlsonTimeZone(SYSDATETIME(), 'Pacific/Fakaofo');

    This fiddle has data that has been pulled from the official Unicode Github repo.