我发现这个函数比使用单独的表或循环的其他解决方案更快。这只是一个基本的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

你可以在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中创建夏令时开始和结束函数

我发现这个函数比使用单独的表或循环的其他解决方案更快。这只是一个基本的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;

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

使用新的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

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