SQL Server 'AT TIME ZONE' Returning Wrong Time Zone

q3qa4bjr 于 2023-06-21 发布在其他

关注(0)|答案(1)|浏览(136)

Using SQL Server 2019, I have a simple query that returns dates from a table as shown below.

The query used to get this data is:

select 
    convert(datetime2(0), SBTRM_BEGIN_DTE, 126) at time zone 'Eastern Standard Time' as 'start_date' --IS0 8601
    , SBTRM_BEGIN_DTE
    , convert(datetime2(0), SBTRM_END_DTE, 126) at time zone 'Eastern Standard Time' at time zone 'Eastern Standard Time' as 'end_date' --IS0 8601
    , SBTRM_END_DTE

Why is the end_dte for any date in December showing at UTC-5:00 instead of UTC-4:00 (Eastern Standard Time)?

Ultimately I'd like dates formatted as 'YYYY-MM-DDTHH:MM:SSZ' to utilize in a data upload. I've tried solutions using format() but this is the closest to what I'm trying to output.

sql-server

来源：https://stackoverflow.com/questions/76493299/at-time-zone-returning-wrong-time-zone

1条答案

按热度按时间

cygmwpex1#

As others have already written, the Daylight Saving Time (DST) is included in the 'TIME ZONE'.

I've always used 'UTC' during my active time.

Here's an example:

WITH DateTable AS (
SELECT * 
FROM (VALUES  ('2022-03-13 00:00:00.000') -- second Sunday in March
            , ('2022-03-14 00:00:00.000')
            , ('2022-03-27 00:00:00.000') -- last Sunday in March (DST Europe)
            , ('2022-03-28 00:00:00.000')
            , ('2022-10-30 00:00:00.000') -- last Sunday in October (DST Europe)
            , ('2022-10-31 00:00:00.000')
            , ('2022-11-06 00:00:00.000') -- first Sunday in November
            , ('2022-11-07 00:00:00.000')
      ) AS t (THE_DATE)
)
SELECT THE_DATE
     -- TIME ZONE with Daylight Saving Time (DST) (sys.time_zone_info.is_currently_dst=1)
     , CONVERT(datetime2(0), THE_DATE, 126) AT TIME ZONE 'Eastern Standard Time' AS 'Eastern Standard Time'
     , CONVERT(datetime2(0), THE_DATE, 126) AT TIME ZONE 'Pacific Standard Time' AS 'Pacific Standard Time'
     , CONVERT(datetime2(0), THE_DATE, 126) AT TIME ZONE 'Central Europe Standard Time' AS 'Central Europe Standard Time'
     -- TIME ZONE without Daylight Saving Time (DST) (sys.time_zone_info.is_currently_dst=0)
     , CONVERT(datetime2(0), THE_DATE, 126) AT TIME ZONE 'Easter Island Standard Time' AS 'Easter Island Standard Time'
     , CONVERT(datetime2(0), THE_DATE, 126) AT TIME ZONE 'Pacific SA Standard Time' AS 'Pacific SA Standard Time'
     -- UTC
     , CONVERT(datetime2(0), THE_DATE, 126) AT TIME ZONE 'UTC' AS 'UTC'
FROM DateTable

In this system table you will find the installed TIME ZONES:

SELECT name, current_utc_offset, is_currently_dst FROM sys.time_zone_info ORDER BY 2

Example

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server 'AT TIME ZONE' Returning Wrong Time Zone

1条答案

相关问题

热门标签

最新问答