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

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

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

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

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

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

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

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

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

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

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

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

sysdatetimeoffset会考虑夏令时

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