从UTC时间计算特定时区的时间

问题描述

我想根据 UTC 中的日期时间计算给定时区的日期时间.

I want to calculate Datetime at given timezone based on Datetime in UTC.

我认为我可以通过以下方式做到这一点:

I thought that I can do it with the following:

DECLARE @timeUTC DATETIME = '2019-01-01 10:00:00'

SELECT 
@timeUTC AS timeUTC,
@timeUTC AT TIME ZONE 'Central European Standard Time' as at_time_zone_offset,
CONVERT(datetime, @timeUTC AT TIME ZONE 'Central European Standard Time',1) at_timezone_convert

-- OUTPUT
---timeUTC                  |at_time_zone_offset                |at_timezone_convert
---2019-01-01 10:00:00.000  |2019-01-01 10:00:00.000 +01:00     |2019-01-01 09:00:00.000

问题是 at_timezone_convert 的结果不正确——当在 UTC 时间是 10:00 时，那么时间 +1 是 11:00，而不是 9.

The problem is that result of at_timezone_convert is incorrect- when at UTC time is 10:00, then time +1 is 11:00, not 9.

我怎样才能得到 2019-01-01 11:00:00.000 的结果?

How can I get the result to be 2019-01-01 11:00:00.000?

推荐答案

Zohar Peled 解释得很好，但为了以防万一，这里是一个代码示例:

Zohar Peled explained it just fine, but just in case, here is a code example:

DECLARE @timeUTC DATETIME = '2019-01-01 10:00:00';

SELECT
    @timeUTC AS timeUTC,
    @timeUTC AT TIME ZONE 'Central European Standard Time' as at_time_zone_offset,
    CONVERT(datetime, cast (@timeUTC AT TIME ZONE 'Central European Standard Time' as datetimeoffset),1) at_timezone_convert,
    CAST(CAST(@timeUTC AS datetimeoffset) AT TIME ZONE 'Central European Standard Time' AS datetime) AS ResultYouNeeded;