对于任何仍然试图解决这个问题的人，这里有一个在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

 declare @mydate2 datetime
 set @mydate2=Getdate()
 select @mydate2 as mydate,
 dateadd(minute, datediff(minute,getdate(),@mydate2),getutcdate())

第一个功能:配置为意大利时区(+1，+2)，切换日期:3月和10月的最后一个星期天，返回当前时区与datetime的差值作为参数。

Returns:
current timezone < parameter timezone ==> +1
current timezone > parameter timezone ==> -1
else 0

代码是:

CREATE FUNCTION [dbo].[UF_ADJUST_OFFSET]
(
    @dt_utc datetime2(7)
)
RETURNS INT
AS
BEGIN


declare @month int,
        @year int,
        @current_offset int,
        @offset_since int,
        @offset int,
        @yearmonth varchar(8),
        @changeoffsetdate datetime2(7)

declare @lastweek table(giorno datetime2(7))

select @current_offset = DATEDIFF(hh, GETUTCDATE(), GETDATE())

select @month = datepart(month, @dt_utc)

if @month < 3 or @month > 10 Begin Set @offset_since = 1 Goto JMP End

if @month > 3 and @month < 10 Begin Set @offset_since = 2 Goto JMP End

--If i'm here is march or october
select @year = datepart(yyyy, @dt_utc)

if @month = 3
Begin

Set @yearmonth = cast(@year as varchar) + '-03-'

Insert Into @lastweek Values(@yearmonth + '31 03:00:00.000000'),(@yearmonth + '30 03:00:00.000000'),(@yearmonth + '29 03:00:00.000000'),(@yearmonth + '28 03:00:00.000000'),
                         (@yearmonth + '27 03:00:00.000000'),(@yearmonth + '26 03:00:00.000000'),(@yearmonth + '25 03:00:00.000000')

--Last week of march
Select @changeoffsetdate = giorno From @lastweek Where  datepart(weekday, giorno) = 1

    if @dt_utc < @changeoffsetdate 
    Begin 
        Set @offset_since = 1 
    End Else Begin
        Set @offset_since = 2
    End
End

if @month = 10
Begin

Set @yearmonth = cast(@year as varchar) + '-10-'

Insert Into @lastweek Values(@yearmonth + '31 03:00:00.000000'),(@yearmonth + '30 03:00:00.000000'),(@yearmonth + '29 03:00:00.000000'),(@yearmonth + '28 03:00:00.000000'),
                         (@yearmonth + '27 03:00:00.000000'),(@yearmonth + '26 03:00:00.000000'),(@yearmonth + '25 03:00:00.000000')

--Last week of october
Select @changeoffsetdate = giorno From @lastweek Where  datepart(weekday, giorno) = 1

    if @dt_utc > @changeoffsetdate 
    Begin 
        Set @offset_since = 1 
    End Else Begin
        Set @offset_since = 2
    End
End

JMP:

if @current_offset < @offset_since Begin
    Set @offset = 1
End Else if @current_offset > @offset_since Set @offset = -1 Else Set @offset = 0

Return @offset

END

然后是转换日期的函数

CREATE FUNCTION [dbo].[UF_CONVERT]
(
    @dt_utc datetime2(7)
)
RETURNS datetime
AS
BEGIN

    declare @offset int


    Select @offset = dbo.UF_ADJUST_OFFSET(@dt_utc)

    if @dt_utc >= '9999-12-31 22:59:59.9999999'
        set @dt_utc = '9999-12-31 23:59:59.9999999'
    Else
        set @dt_utc = (SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), @dt_utc) )

    if @offset <> 0
        Set @dt_utc = dateadd(hh, @offset, @dt_utc)

    RETURN @dt_utc

END

最简单的答案并不总是在底部，但这一次是，并且可以在上面的评论中看到。 使用您自己的“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。

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

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

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

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

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