作为一个警告-如果你要使用以下(注意毫秒而不是分钟):

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

请记住，DATEDIFF部分并不总是返回相同的数字。所以不要用它来将DateTimes精确到毫秒。

对于任何仍然试图解决这个问题的人，这里有一个在SQL Server 2017中工作的概念证明

 declare
    @StartDate date = '2020-01-01'

;with cte_utc as
(
    select 
         1 as i
        ,CONVERT(datetime, @StartDate) AS UTC
        ,datepart(weekday, CONVERT(datetime, @StartDate)) as Weekday
        ,datepart(month, CONVERT(datetime, @StartDate)) as [Month]
        ,datepart(YEAR, CONVERT(datetime, @StartDate)) as [Year]
        
    union all

    Select
         i + 1
        ,dateadd(d, 1, utc)
        ,datepart(weekday, CONVERT(datetime, dateadd(d, 1, utc))) as Weekday
        ,datepart(month, CONVERT(datetime, dateadd(d, 1, utc))) as [Month]
        ,datepart(YEAR, CONVERT(datetime, dateadd(d, 1, utc))) as [Year]
    from    
        cte_utc
    where
        (i + 1) < 32767

), cte_utc_dates as 
(
    select 
        *,
        DENSE_RANK()OVER(PARTITION BY [Year], [Month], [Weekday] ORDER BY Utc) WeekDayIndex
    from
        cte_utc

), cte_hours as (
    select 0 as [Hour]
    union all
    select [Hour] + 1 from cte_hours where [Hour] < 23
)

select
    d.*
    , DATEADD(hour, h.Hour, d.UTC) AS UtcTime
    ,CONVERT(datetime, DATEADD(hour, h.Hour, d.UTC) AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time') CST
    ,CONVERT(datetime, DATEADD(hour, h.Hour, d.UTC) AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') EST
from
    cte_utc_dates d, cte_hours h
where
    ([Month] = 3 and [Weekday] = 1 and WeekDayIndex = 2 )-- dst start
    or 
    ([Month] = 11 and [Weekday] = 1 and WeekDayIndex = 1 )-- dst end
order by
    utc
OPTION (MAXRECURSION 32767)

GO

下面是一个将夏令时考虑在内的更简单的例子

CREATE FUNCTION [dbo].[UtcToLocal] 
(
    @p_utcDatetime DATETIME 
)
RETURNS DATETIME
AS
BEGIN
    RETURN DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), @p_utcDatetime), GETDATE())
END

我发现这个函数比使用单独的表或循环的其他解决方案更快。这只是一个基本的case语句。考虑到4月到10月之间的所有月份都有-4小时的偏移(东部时间)，我们只需要为边缘日添加更多的案例线。否则，偏移量为-5小时。

这是特定于从UTC到东部时间的转换，但可以根据需要添加其他时区功能。

USE [YourDatabaseName]
GO

/****** Object:  UserDefinedFunction [dbo].[ConvertUTCtoEastern]    Script Date: 11/2/2016 5:21:52 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[ConvertUTCtoEastern]
(
@dtStartDate DATETIME
)
RETURNS DATETIME
AS
BEGIN
DECLARE @Working DATETIME
DECLARE @Returned DATETIME

SET @Working = @dtStartDate
SET @Working = 
case when month(@Working) between 4 and 10 then dateadd(HH,-4,@Working) 
     when @Working between '2017-03-12' and '2017-11-05' then dateadd(HH,-4,@Working) 
     when @Working between '2016-03-13' and '2016-11-06' then dateadd(HH,-4,@Working) 
     when @Working between '2015-03-08' and '2015-11-01' then dateadd(HH,-4,@Working) 
     when @Working between '2014-03-09' and '2014-11-02' then dateadd(HH,-4,@Working) 
     when @Working between '2013-03-10' and '2013-11-03' then dateadd(HH,-4,@Working) 
     when @Working between '2012-03-11' and '2012-11-04' then dateadd(HH,-4,@Working) 
else dateadd(HH,-5,@Working) end

SET @Returned = @Working

RETURN @Returned

END


GO

您必须重新格式化字符串以及转换为正确的时间。在这种情况下，我需要祖鲁时间。

Declare @Date datetime;
Declare @DateString varchar(50);
set @Date = GETDATE(); 
declare @ZuluTime datetime;

Declare @DateFrom varchar (50);
Declare @DateTo varchar (50);
set @ZuluTime = DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), @Date);
set @DateString =  FORMAT(@ZuluTime, 'yyyy-MM-ddThh:mm:ssZ', 'en-US' )  
select @DateString;

最简单的答案并不总是在底部，但这一次是，并且可以在上面的评论中看到。 使用您自己的“AT TIME ZONE”来捕获列/数据字段的TzOffset，而不是当前的SYSDATETIME。 在下面的数据中，2个查询，一个关于feb数据(DST是关闭的，在阿姆斯特丹的冬天)+1差异 第二次查询阿姆斯特丹4月份的数据，所以+2小时的差异。

    select top 2 month(receiveTimeUTC) as MonthInWinterOrSpring
   ,  receiveTimeUTC
   ,  CONVERT(datetime,  SWITCHOFFSET(CONVERT(datetimeoffset,  receiveTimeUTC),  DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as LocalTimeWrongNoDST
   ,  CONVERT(datetime,  SWITCHOFFSET(CONVERT(datetimeoffset,  receiveTimeUTC),  DATENAME(TzOffset, receiveTimeUTC  AT TIME ZONE 'Central European Standard Time' ))) as LocalTimeWithDST
       from sensordetails order by id

    select top 2 month(receiveTimeUTC) as MonthInWinterOrSpring, receiveTimeUTC
,  CONVERT(datetime,  SWITCHOFFSET(CONVERT(datetimeoffset,  receiveTimeUTC),  DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as LocalTimeWrongNoDST
,  CONVERT(datetime,  SWITCHOFFSET(CONVERT(datetimeoffset,  receiveTimeUTC),  DATENAME(TzOffset, receiveTimeUTC  AT TIME ZONE 'Central European Standard Time' ))) as LocalTimeWithDST
       from sensordetails order by id desc

结果:

所以这是一个T-SQL (SQL Server Answer)，不需要函数的storedproc。