'How to use 'America/New_York ' in At Time Zone in SQL

I have this piece of code that works fine in SQL. However I would like to use a different time zone format like 'America/New_York' instead of 'US Eastern Standard Time'

SELECT TODATETIMEOFFSET(CAST(CURRENT_TIMESTAMP as datetime), -5) AT TIME ZONE 'US Eastern Standard Time'
FROM dual
sqlsql-servertimezone


Solution 1:[1]

Presently, SQL Server only supports Windows time zone identifiers. You can vote for adding IANA time zone support here.

In the meantime, you have options:

  • In your application layer, you can convert the IANA time zone ID to the equivalent Windows time zone ID. If you are using .NET, the simplest way is with my TimeZoneConverter library. Otherwise, you can use the CLDR mappings file manually, or any library implementation that is based on it.

  • You could use my SQL Server Time Zone Support project, which uses custom tables and functions instead of the AT TIME ZONE statement.

  • You could move all your time zone conversion logic into your application layer instead of the database.

Also, minor point - US Eastern Standard Time maps to America/Indianapolis. You probably want Eastern Standard Time, which maps to America/New_York.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Anssssss

Related Questions