如何在 SQL 中的 At Time Zone 中使用“America/New_York" [英] How to use 'America/New_York ' in At Time Zone in SQL

问题描述

我有一段在 SQL 中运行良好的代码.但是我想使用不同的时区格式，例如 'America/New_York' 而不是 'US Eastern Standard Time'

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

推荐答案

目前，SQL Server 仅支持 Windows 时区标识符.您可以在此处投票支持添加 IANA 时区.

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:

• 在您的应用程序层，您可以将 IANA 时区 ID 转换为等效的 Windows 时区 ID.如果您使用 .NET，最简单的方法是使用我的 TimeZoneConverter 库.否则，您可以使用 CLDR 映射文件 手动，或基于它的任何库实现.

• 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.

您可以使用我的 SQL Server 时区支持 项目，该项目使用自定义表和函数而不是 AT TIME ZONE 语句.

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.

此外，次要点 - 美国东部标准时间 映射到 America/Indianapolis.您可能需要 东部标准时间，它映射到 America/New_York.

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

这篇关于如何在 SQL 中的 At Time Zone 中使用“America/New_York"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！