如果您将数据存储为UTC日期在数据库中，您可以做一些简单的事情

select 
 [MyUtcDate] + getdate() - getutcdate()
from [dbo].[mytable]

这是从服务器的角度来看，它总是本地的，你不会笨拙地使用AT TIME ZONE '你的时区名称'， 如果您的数据库像客户端安装一样被移动到另一个时区，那么硬编码的时区可能会让您感到困扰。

我发现当有大量数据时，一次性函数的方法太慢了。因此，我通过连接到一个允许计算小时差的表函数来实现它，它基本上是带有小时偏移量的datetime分段。一年是4行。这个表格函数

dbo.fn_getTimeZoneOffsets('3/1/2007 7:00am', '11/5/2007 9:00am', 'EPT')

将返回这个表:

startTime          endTime   offset  isHr2
3/1/07 7:00     3/11/07 6:59    -5    0
3/11/07 7:00    11/4/07 6:59    -4    0
11/4/07 7:00    11/4/07 7:59    -5    1
11/4/07 8:00    11/5/07 9:00    -5    0

它确实考虑了夏时制。下面是它如何使用的示例，完整的博客文章在这里。

select mt.startTime as startUTC, 
    dateadd(hh, tzStart.offset, mt.startTime) as startLocal, 
    tzStart.isHr2
from MyTable mt 
inner join dbo.fn_getTimeZoneOffsets(@startViewUTC, @endViewUTC, @timeZone)  tzStart
on mt.startTime between tzStart.startTime and tzStart.endTime

我有代码执行UTC到本地和本地到UTC时间，允许使用这样的代码进行转换

DECLARE @usersTimezone VARCHAR(32)='Europe/London'
DECLARE @utcDT DATETIME=GetUTCDate()
DECLARE @userDT DATETIME=[dbo].[funcUTCtoLocal](@utcDT, @usersTimezone)

and

DECLARE @usersTimezone VARCHAR(32)='Europe/London'
DECLARE @userDT DATETIME=GetDate()
DECLARE @utcDT DATETIME=[dbo].[funcLocaltoUTC](@userDT, @usersTimezone)

这些函数可以支持NodaTime提供的IANA/TZDB中的全部或一部分时区-请参阅https://nodatime.org/TimeZones上的完整列表

请注意，我的用例意味着我只需要一个“当前”窗口，允许在大约+/- 5年的范围内转换时间。这意味着，如果您需要很宽的时间段，我使用的方法可能不适合您，因为它在给定日期范围内为每个时区间隔生成代码的方式不同。

该项目位于GitHub: https://github.com/elliveny/SQLServerTimeConversion

这将生成如下示例所示的SQL函数代码

我没有发现任何这些示例有助于将日期时间存储为UTC到指定时区(不是服务器的时区，因为Azure SQL数据库以UTC运行)中的日期时间。我是这样处理的。它并不优雅，但它很简单，无需维护其他表就能给出正确答案:

select CONVERT(datetime, SWITCHOFFSET(dateTimeField, DATEPART(TZOFFSET, 
dateTimeField AT TIME ZONE 'Eastern Standard Time')))

这可以在没有函数的情况下完成。下面的代码将把UTC时间转换为考虑夏令时的山地时间。相应地调整所有的-6和-7数字到您的时区(即对于EST，您将分别调整为-4和-5)

--Adjust a UTC value, in the example the UTC field is identified as UTC.Field, to account for daylight savings time when converting out of UTC to Mountain time.
CASE
    --When it's between March and November, it is summer time which is -6 from UTC
    WHEN MONTH ( UTC.Field ) > 3 AND MONTH ( UTC.Field ) < 11 
        THEN DATEADD ( HOUR , -6 , UTC.Field )
    --When its March and the day is greater than the 14, you know it's summer (-6)
    WHEN MONTH ( UTC.Field ) = 3
        AND DATEPART ( DAY , UTC.Field ) >= 14 
        THEN
            --However, if UTC is before 9am on that Sunday, then it's before 2am Mountain which means it's still Winter daylight time.
            CASE 
                WHEN DATEPART ( WEEKDAY , UTC.Field ) = 1 
                    AND UTC.Field < '9:00'
                    --Before 2am mountain time so it's winter, -7 hours for Winter daylight time
                    THEN DATEADD ( HOUR , -7 , UTC.Field )
                --Otherwise -6 because it'll be after 2am making it Summer daylight time
                ELSE DATEADD ( HOUR , -6 , UTC.Field )
            END
    WHEN MONTH ( UTC.Field ) = 3
        AND ( DATEPART ( WEEKDAY , UTC.Field ) + 7 ) <= DATEPART ( day , UTC.Field ) 
        THEN 
            --According to the date, it's moved onto Summer daylight, but we need to account for the hours leading up to 2am if it's Sunday
            CASE 
                WHEN DATEPART ( WEEKDAY , UTC.Field ) = 1 
                    AND UTC.Field < '9:00'
                    --Before 9am UTC is before 2am Mountain so it's winter Daylight, -7 hours
                    THEN DATEADD ( HOUR , -7 , UTC.Field )
                --Otherwise, it's summer daylight, -6 hours
                ELSE DATEADD ( HOUR , -6 , UTC.Field )
            END
    --When it's November and the weekday is greater than the calendar date, it's still Summer so -6 from the time
    WHEN MONTH ( UTC.Field ) = 11
        AND DATEPART ( WEEKDAY , UTC.Field ) > DATEPART ( DAY , UTC.Field ) 
        THEN DATEADD ( HOUR , -6 , UTC.Field )
    WHEN MONTH ( UTC.Field ) = 11
        AND DATEPART ( WEEKDAY , UTC.Field ) <= DATEPART ( DAY , UTC.Field ) 
            --If the weekday is less than or equal to the calendar day it's Winter daylight but we need to account for the hours leading up to 2am.
            CASE 
                WHEN DATEPART ( WEEKDAY , UTC.Field ) = 1 
                    AND UTC.Field < '8:00'
                    --If it's before 8am UTC and it's Sunday in the logic outlined, then it's still Summer daylight, -6 hours
                    THEN DATEADD ( HOUR , -6 , UTC.Field )
                --Otherwise, adjust for Winter daylight at -7
                ELSE DATEADD ( HOUR , -7 , UTC.Field )
            END
    --If the date doesn't fall into any of the above logic, it's Winter daylight, -7
    ELSE
        DATEADD ( HOUR , -7 , UTC.Field )
END

如果你需要一个转换而不是你的服务器位置，这里有一个函数，允许你传递一个标准偏移量，并考虑美国夏令时:

-- =============================================
-- Author:      Ron Smith
-- Create date: 2013-10-23
-- Description: Converts UTC to DST
--              based on passed Standard offset
-- =============================================
CREATE FUNCTION [dbo].[fn_UTC_to_DST]
(
    @UTC datetime,
    @StandardOffset int
)
RETURNS datetime
AS
BEGIN

    declare 
        @DST datetime,
        @SSM datetime, -- Second Sunday in March
        @FSN datetime  -- First Sunday in November

    -- get DST Range
    set @SSM = datename(year,@UTC) + '0314' 
    set @SSM = dateadd(hour,2,dateadd(day,datepart(dw,@SSM)*-1+1,@SSM))
    set @FSN = datename(year,@UTC) + '1107'
    set @FSN = dateadd(second,-1,dateadd(hour,2,dateadd(day,datepart(dw,@FSN)*-1+1,@FSN)))

    -- add an hour to @StandardOffset if @UTC is in DST range
    if @UTC between @SSM and @FSN
        set @StandardOffset = @StandardOffset + 1

    -- convert to DST
    set @DST = dateadd(hour,@StandardOffset,@UTC)

    -- return converted datetime
    return @DST

END

GO