如果你的本地日期时间是东部标准时间，你想从UTC转换为UTC，那么在Azure SQL和SQL Server 2016及以上版本中，你可以这样做:

SELECT YourUtcColumn AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS
       LocalTime
FROM   YourTable

时区名称的完整列表可以在以下地址找到:

SELECT * FROM sys.time_zone_info 

是的，时区的命名很糟糕——即使是东部标准时间，也考虑到了夏令时。

下面的版本考虑了夏令时、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

这些对我来说都没用，但下面这个100%有效。希望这能帮助其他像我一样尝试转换它的人。

CREATE FUNCTION [dbo].[fn_UTC_to_EST]
(
    @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 = DATEADD(dd,7 + (6-(DATEDIFF(dd,0,DATEADD(mm,(YEAR(GETDATE())-1900) * 12 + 2,0))%7)),DATEADD(mm,(YEAR(GETDATE())-1900) * 12 + 2,0))+'02:00:00' 
set @FSN = DATEADD(dd, (6-(DATEDIFF(dd,0,DATEADD(mm,(YEAR(GETDATE())-1900) * 12 + 10,0))%7)),DATEADD(mm,(YEAR(GETDATE())-1900) * 12 + 10,0)) +'02:00:00'

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

这样就可以获得与夏令时同步的服务器时间

declare @dt datetime
set @dt = getutcdate() -- GMT equivalent

sysdatetimeoffset会考虑夏令时

select [InputTime] = @dt
       , [LocalTime2] = dateadd(mi, datediff(mi, sysdatetimeoffset(),getdate()), @dt) 

对甲骨文来说最好的方法:

硬编码的datetime:

SELECT TO_CHAR(CAST((FROM_TZ(CAST(TO_DATE('2018-10-27 21:00', 'YYYY-MM-DD HH24:MI') AS TIMESTAMP), 'UTC') AT  TIME ZONE 'EET') AS DATE), 'YYYY-MM-DD HH24:MI') UTC_TO_EET FROM DUAL

结果: 2018-10-28 00:00

列名和表名:

SELECT TO_CHAR(CAST((FROM_TZ(CAST(COLUMN_NAME AS TIMESTAMP), 'UTC') AT  TIME ZONE 'EET') AS DATE), 'YYYY-MM-DD HH24:MI') UTC_TO_EET FROM TABLE_NAME

对于Azure SQL和@@Version >= SQL Server 2016用户，下面是一个使用AT TIME ZONE的简单函数。

CREATE FUNCTION [dbo].[Global_Convert_UTCTimeTo_LocalTime]
(
   @LocalTimeZone        VARCHAR(50),
   @UTCDateTime          DATETIME
)
RETURNS DATETIME
AS
BEGIN
   DECLARE @ConvertedDateTime DATETIME;

   SELECT @ConvertedDateTime = @UTCDateTime AT TIME ZONE 'UTC' AT TIME ZONE @LocalTimeZone
   RETURN @ConvertedDateTime

END
GO

对于@LocalTimeZone可以采用的值类型，请转到此链接或转到KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones