罗恩的回答有一个错误。它使用当地时间凌晨2:00，其中需要UTC等效值。我没有足够的声望分数来评论Ron的回答，所以更正版本如下:

-- =============================================
-- 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 - @StandardOffset,dateadd(day,datepart(dw,@SSM)*-1+1,@SSM))
set @FSN = datename(year,@UTC) + '1107'
set @FSN = dateadd(second,-1,dateadd(hour,2 - (@StandardOffset + 1),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

该函数将UTC时间转换为EST时间，并进行DST调整。你可以在这个函数中更改你设计的时区名称，或者从注册表中获取:

Create Function fnConvertUTCTimetoESTTime(
    @UTCTime as datetime
)
returns datetime
as
begin
    return convert(datetime, convert(varchar(23), @UTCTime AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time', 121), 121)
end
go

select dbo.fnConvertUTCTimetoESTTime ('2020-3-8 5:00:00.000')
    , dbo.fnConvertUTCTimetoESTTime ('2020-3-8 6:00:00.000')
    , dbo.fnConvertUTCTimetoESTTime ('2020-3-8 7:00:00.000')
    , dbo.fnConvertUTCTimetoESTTime ('2020-3-8 8:00:00.000')

--returns 0:00am, 1:00am, 3:00am, 4:00am

select dbo.fnConvertUTCTimetoESTTime ('2020-11-1 4:00:00.000')
    , dbo.fnConvertUTCTimetoESTTime ('2020-11-1 5:00:00.000')
    , dbo.fnConvertUTCTimetoESTTime ('2020-11-1 6:00:00.000')
    , dbo.fnConvertUTCTimetoESTTime ('2020-11-1 7:00:00.000')

--returns 0:00am, 1:00am, 1:00am, 2:00am

请注意，您不能只是返回“@UTCTime AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard TIME '”作为结果，因为该结果实际上是EST格式的UTC时间(当您比较这个“假”EST时间或将其包含在order子句中时，它将被转换回UTC时间)。

你可以在SQL Server 2008或更高版本上这样做:

SELECT CONVERT(datetime, 
               SWITCHOFFSET(CONVERT(datetimeoffset, 
                                    MyTable.UtcColumn), 
                            DATENAME(TzOffset, SYSDATETIMEOFFSET()))) 
       AS ColumnInLocalTime
FROM MyTable

你也可以用更简洁的方法:

SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), MyTable.UtcColumn) 
       AS ColumnInLocalTime
FROM MyTable

无论您做什么，都不要使用-来减去日期，因为该操作不是原子的，并且由于在不同时间(即非原子地)检查的系统datetime和本地datetime之间的竞争条件，您有时会得到不确定的结果。

请注意，这个答案没有考虑夏令时。如果你想包含夏令时调整，也请参阅以下SO问题:

如何在SQL Server中创建夏令时开始和结束函数

下面的版本考虑了夏令时、UTC抵消，并且没有锁定到特定的年份。

---------------------------------------------------------------------------------------------------
--Name:     udfToLocalTime.sql
--Purpose:  To convert UTC to local US time accounting for DST
--Author:   Patrick Slesicki
--Date:     3/25/2014
--Notes:    Works on SQL Server 2008R2 and later, maybe SQL Server 2008 as well.
--          Good only for US States observing the Energy Policy Act of 2005.
--          Function doesn't apply for years prior to 2007.
--          Function assumes that the 1st day of the week is Sunday.
--Tests:        
--          SELECT dbo.udfToLocalTime('2014-03-09 9:00', DEFAULT)
--          SELECT dbo.udfToLocalTime('2014-03-09 10:00', DEFAULT)
--          SELECT dbo.udfToLocalTime('2014-11-02 8:00', DEFAULT)
--          SELECT dbo.udfToLocalTime('2014-11-02 9:00', DEFAULT)
---------------------------------------------------------------------------------------------------
ALTER FUNCTION udfToLocalTime
    (
    @UtcDateTime    AS DATETIME
    ,@UtcOffset     AS INT = -8 --PST
    )
RETURNS DATETIME
AS 
BEGIN
    DECLARE 
        @PstDateTime    AS DATETIME
        ,@Year          AS CHAR(4)
        ,@DstStart      AS DATETIME
        ,@DstEnd        AS DATETIME
        ,@Mar1          AS DATETIME
        ,@Nov1          AS DATETIME
        ,@MarTime       AS TIME
        ,@NovTime       AS TIME
        ,@Mar1Day       AS INT
        ,@Nov1Day       AS INT
        ,@MarDiff       AS INT
        ,@NovDiff       AS INT

    SELECT
        @Year       = YEAR(@UtcDateTime)
        ,@MarTime   = CONVERT(TIME, DATEADD(HOUR, -@UtcOffset, '1900-01-01 02:00'))
        ,@NovTime   = CONVERT(TIME, DATEADD(HOUR, -@UtcOffset - 1, '1900-01-01 02:00'))
        ,@Mar1      = CONVERT(CHAR(16), @Year + '-03-01 ' + CONVERT(CHAR(5), @MarTime), 126)
        ,@Nov1      = CONVERT(CHAR(16), @Year + '-11-01 ' + CONVERT(CHAR(5), @NovTime), 126)
        ,@Mar1Day   = DATEPART(WEEKDAY, @Mar1)
        ,@Nov1Day   = DATEPART(WEEKDAY, @Nov1)

    --Get number of days between Mar 1 and DST start date
    IF @Mar1Day = 1 SET @MarDiff = 7
    ELSE SET @MarDiff = 15 - @Mar1Day

    --Get number of days between Nov 1 and DST end date
    IF @Nov1Day = 1 SET @NovDiff = 0
    ELSE SET @NovDiff = 8 - @Nov1Day

    --Get DST start and end dates
    SELECT 
        @DstStart   = DATEADD(DAY, @MarDiff, @Mar1)
        ,@DstEnd    = DATEADD(DAY, @NovDiff, @Nov1)

    --Change UTC offset if @UtcDateTime is in DST Range
    IF @UtcDateTime >= @DstStart AND @UtcDateTime < @DstEnd SET @UtcOffset = @UtcOffset + 1

    --Get Conversion
    SET @PstDateTime = DATEADD(HOUR, @UtcOffset, @UtcDateTime)
    RETURN @PstDateTime
END
GO

如果您将数据存储为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