Search code examples
sqlsql-serveroracle-databasetimezoneoffset

SQL Server variant from Oracle TZ_OFFSET

The execution of following SQL in Oracle:

SELECT TZ_OFFSET('Europe/Brussels') from dual;

returns the value +02:00

Note that this function can be used for any timezone in Olsson notation. It's not about the database server.

I was wondering if there exists a similar function in MS Sql Server where you can ask the offset of a given timezone (in the microsoft timezone notation).

Solution

  • From SQL 2016 onward, and in Azure SQL Database, you can achieve this with:

    SELECT DATENAME(tz, SYSDATETIMEOFFSET() AT TIME ZONE 'Romance Standard Time')

-- Output: '+02:00'

    Notice the time zone names are Windows time zone identifiers, not the IANA TZ (aka Olson) identifiers. For more on the differences, see the timezone tag wiki.

    If you want to use IANA TZ identifiers, or if you are using an older version of SQL Server that doesn't have the AT TIME ZONE function, then you can use my SQL Server Time Zone Support project, with the following:

    SELECT DATENAME(tz, Tzdb.SwitchZone(SYSDATETIMEOFFSET(), 'Europe/Brussels'))

-- Output: '+02:00'

    Also, note that an offset can only be computed for a time zone at a particular reference point. In my examples, you can see I have chosen the current system time with SYSDATETIMEOFFSET(). (The current time is implicit with Oracle's TZ_OFFSET function.)