我发现当有大量数据时，一次性函数的方法太慢了。因此，我通过连接到一个允许计算小时差的表函数来实现它，它基本上是带有小时偏移量的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

第一个功能:配置为意大利时区(+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

如果在数据库上启用CLR以及使用sql server的时区是一个选项，那么可以很容易地在. net中编写它。

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlDateTime fn_GetLocalFromUTC(SqlDateTime UTC)
    {
        if (UTC.IsNull)
            return UTC;

        return new SqlDateTime(UTC.Value.ToLocalTime());
    }
}

输入一个UTC日期时间值，然后输出相对于服务器的本地日期时间值。Null值返回Null。

使用新的SQL Server 2016机会:

CREATE FUNCTION ToLocalTime(@dtUtc datetime, @timezoneId nvarchar(256))
RETURNS datetime
AS BEGIN

return @dtUtc AT TIME ZONE 'UTC' AT TIME ZONE @timezoneId

/* -- second way, faster

return SWITCHOFFSET(@dtUtc , DATENAME(tz, @dtUtc AT TIME ZONE @timezoneId))

*/

/* -- third way

declare @dtLocal datetimeoffset
set @dtLocal = @dtUtc AT TIME ZONE @timezoneId
return dateadd(minute, DATEPART (TZoffset, @dtLocal), @dtUtc)

*/

END
GO

但clr程序的工作速度快5倍:'-(

请注意，一个时区的偏移量可以更改为冬季时间或夏季时间。例如

select cast('2017-02-08 09:00:00.000' as datetime) AT TIME ZONE 'Eastern Standard Time'
select cast('2017-08-08 09:00:00.000' as datetime) AT TIME ZONE 'Eastern Standard Time'

结果:

2017-02-08 09:00:00.000 -05:00
2017-08-08 09:00:00.000 -04:00

你不能只是添加常数偏移量。

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

罗恩的回答有一个错误。它使用当地时间凌晨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