对于 DST 之前或之后的日期，如何获得 UTC 和本地时间之间的正确偏移量？

在 SQL Server 2016 之前，将非当前 UTC 日期转换为本地时间的最佳方法是使用 Microsoft .Net 公共语言运行时或 CLR。

代码本身很简单；困难的部分通常是让人们相信 CLR 不是纯粹的邪恶或可怕......

对于众多示例之一，请查看Harsh Chawla 关于该主题的博客文章。

不幸的是，在 SQL Server 2016 之前没有任何内置可以处理这种类型的转换，除了基于 CLR 的解决方案。你可以编写一个 T-SQL 函数来做这样的事情，但是你必须自己实现日期更改逻辑，我认为这绝对不容易。

我在 codeplex 上开发并发布了T-SQL Toolbox项目，以帮助任何在 Microsoft SQL Server 中处理日期时间和时区问题的人。它是开源的，完全免费使用。

它使用简单的 T-SQL（无 CLR）以及开箱即用的预填充配置表提供简单的日期时间转换 UDF。它具有完整的 DST（夏令时）支持。

可以在表“DateTimeUtil.Timezone”（在 T-SQL 工具箱数据库中提供）中找到所有受支持时区的列表。

在您的示例中，您可以使用以下示例：

SELECT [DateTimeUtil].[UDF_ConvertUtcToLocalByTimezoneIdentifier] (
    'W. Europe Standard Time', -- the target local timezone
    '2014-03-30 00:55:00' -- the original UTC datetime you want to convert
)

这将返回转换后的本地日期时间值。

不幸的是，SQL Server 2008 或更高版本支持它只是因为更新的数据类型（DATE、TIME、DATETIME2）。但由于提供了完整的源代码，您可以通过将表和 UDF 替换为 DATETIME 轻松调整它们。我没有可用于测试的 MSSQL 2005，但它也应该适用于 MSSQL 2005。如有问题，请告诉我。

我总是使用这个 TSQL 命令。

-- the utc value 
declare @utc datetime = '20/11/2014 05:14'

-- the local time

select DATEADD(hh, DATEDIFF(hh, getutcdate(), getdate()), @utc)

-- or if you're concerned about non-whole-hour offsets, use:

SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), GETDATE()), @utc).

它非常简单，它可以完成这项工作。

对于 SQL Server 2016+，您可以使用AT TIME ZONE。它将自动处理夏令时。

我在 StackOverflow 上找到了这个答案，它提供了一个用户定义的函数，似乎可以准确地翻译日期时间

您唯一需要修改的是@offset顶部的变量，以将其设置为运行此函数的 SQL 服务器的时区偏移量。就我而言，我们的 SQL 服务器使用 EST，即 GMT - 5

它并不完美，可能不适用于许多情况，例如半小时或 15 分钟的 TZ 偏移量（对于那些我推荐像Kevin 推荐的 CLR 函数的情况），但它适用于北方的大多数通用时区美国。

CREATE FUNCTION [dbo].[UDTToLocalTime](@UDT AS DATETIME)  
RETURNS DATETIME
AS
BEGIN 
--====================================================
--Set the Timezone Offset (NOT During DST [Daylight Saving Time])
--====================================================
DECLARE @Offset AS SMALLINT
SET @Offset = -5

--====================================================
--Figure out the Offset Datetime
--====================================================
DECLARE @LocalDate AS DATETIME
SET @LocalDate = DATEADD(hh, @Offset, @UDT)

--====================================================
--Figure out the DST Offset for the UDT Datetime
--====================================================
DECLARE @DaylightSavingOffset AS SMALLINT
DECLARE @Year as SMALLINT
DECLARE @DSTStartDate AS DATETIME
DECLARE @DSTEndDate AS DATETIME
--Get Year
SET @Year = YEAR(@LocalDate)

--Get First Possible DST StartDay
IF (@Year > 2006) SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-03-08 02:00:00'
ELSE              SET @DSTStartDate = CAST(@Year AS CHAR(4)) + '-04-01 02:00:00'
--Get DST StartDate 
WHILE (DATENAME(dw, @DSTStartDate) <> 'sunday') SET @DSTStartDate = DATEADD(day, 1,@DSTStartDate)


--Get First Possible DST EndDate
IF (@Year > 2006) SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-11-01 02:00:00'
ELSE              SET @DSTEndDate = CAST(@Year AS CHAR(4)) + '-10-25 02:00:00'
--Get DST EndDate 
WHILE (DATENAME(dw, @DSTEndDate) <> 'sunday') SET @DSTEndDate = DATEADD(day,1,@DSTEndDate)

--Get DaylightSavingOffset
SET @DaylightSavingOffset = CASE WHEN @LocalDate BETWEEN @DSTStartDate AND @DSTEndDate THEN 1 ELSE 0 END

--====================================================
--Finally add the DST Offset 
--====================================================
RETURN DATEADD(hh, @DaylightSavingOffset, @LocalDate)
END



GO

对于 Stack Overflow 上提出的类似问题，有几个很好的答案。我最终使用Bob Albright 的第二个答案中的 T-SQL 方法来清理由数据转换顾问造成的混乱。

它几乎适用于我们所有的数据，但后来我意识到他的算法只适用于早在 April 5, 1987 的日期，而且我们有一些 1940 年代的日期仍然没有正确转换。我们最终需要 SQL Server 数据库中的日期与使用 Java API 转换为本地时间UTC的第三方程序中的算法对齐。UTC

我喜欢Kevin Feasel 使用 Harsh Chawla 的示例的答案CLR中的示例，并且我还想将它与使用 Java 的解决方案进行比较，因为我们的前端使用 Java 进行本地时间转换。UTC

维基百科提到 1987 年之前涉及时区调整的 8 项不同的宪法修正案，其中许多都非常本地化到不同的州，因此 CLR 和 Java 有可能对它们进行不同的解释。您的前端应用程序代码是否使用 dotnet 或 Java，或者 1987 年之前的日期对您来说是个问题？

您可以使用 CLR 存储过程轻松完成此操作。

[SqlFunction]
public static SqlDateTime ToLocalTime(SqlDateTime UtcTime, SqlString TimeZoneId)
{
    if (UtcTime.IsNull)
        return UtcTime;

    var timeZone = TimeZoneInfo.FindSystemTimeZoneById(TimeZoneId.Value);
    var localTime = TimeZoneInfo.ConvertTimeFromUtc(UtcTime.Value, timeZone);
    return new SqlDateTime(localTime);
}

您可以将可用的时区存储在表中：

CREATE TABLE TimeZones
(
    TimeZoneId NVARCHAR(32) NOT NULL CONSTRAINT PK_TimeZones PRIMARY KEY,
    DisplayName NVARCHAR(64) NOT NULL,
    SupportsDaylightSavingTime BIT NOT NULL,
)

这个存储过程将用您服务器上可能的时区填充表格。

public partial class StoredProcedures
{
    [SqlProcedure]
    public static void PopulateTimezones()
    {
        using (var sql = new SqlConnection("Context Connection=True"))
        {
            sql.Open();

            using (var cmd = sql.CreateCommand())
            {
                cmd.CommandText = "DELETE FROM TimeZones";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "INSERT INTO [dbo].[TimeZones]([TimeZoneId], [DisplayName], [SupportsDaylightSavingTime]) VALUES(@TimeZoneId, @DisplayName, @SupportsDaylightSavingTime);";
                var Id = cmd.Parameters.Add("@TimeZoneId", SqlDbType.NVarChar);
                var DisplayName = cmd.Parameters.Add("@DisplayName", SqlDbType.NVarChar);
                var SupportsDaylightSavingTime = cmd.Parameters.Add("@SupportsDaylightSavingTime", SqlDbType.Bit);

                foreach (var zone in TimeZoneInfo.GetSystemTimeZones())
                {
                    Id.Value = zone.Id;
                    DisplayName.Value = zone.DisplayName;
                    SupportsDaylightSavingTime.Value = zone.SupportsDaylightSavingTime;

                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}

SQL Server 2016 版将一劳永逸地解决这个问题。对于早期版本，CLR 解决方案可能是最简单的。或者对于特定的 DST 规则（仅限美国），T-SQL 函数可以相对简单。

但是，我认为通用的 T-SQL 解决方案可能是可能的。只要xp_regread有效，试试这个：

CREATE TABLE #tztable (Value varchar(50), Data binary(56));
DECLARE @tzname varchar(150) = 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation'
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @tzname, 'TimeZoneKeyName', @tzname OUT;
SELECT @tzname = 'SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones\' + @tzname
INSERT INTO #tztable
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @tzname, 'TZI';
SELECT                                                                                  -- See http://msdn.microsoft.com/ms725481
 CAST(CAST(REVERSE(SUBSTRING(Data,  1, 4)) AS binary(4))      AS int) AS BiasMinutes,   -- UTC = local + bias: > 0 in US, < 0 in Europe!
 CAST(CAST(REVERSE(SUBSTRING(Data,  5, 4)) AS binary(4))      AS int) AS ExtraBias_Std, --   0 for most timezones
 CAST(CAST(REVERSE(SUBSTRING(Data,  9, 4)) AS binary(4))      AS int) AS ExtraBias_DST, -- -60 for most timezones: DST makes UTC 1 hour earlier
 -- When DST ends:
 CAST(CAST(REVERSE(SUBSTRING(Data, 13, 2)) AS binary(2)) AS smallint) AS StdYear,       -- 0 = yearly (else once)
 CAST(CAST(REVERSE(SUBSTRING(Data, 15, 2)) AS binary(2)) AS smallint) AS StdMonth,      -- 0 = no DST
 CAST(CAST(REVERSE(SUBSTRING(Data, 17, 2)) AS binary(2)) AS smallint) AS StdDayOfWeek,  -- 0 = Sunday to 6 = Saturday
 CAST(CAST(REVERSE(SUBSTRING(Data, 19, 2)) AS binary(2)) AS smallint) AS StdWeek,       -- 1 to 4, or 5 = last <DayOfWeek> of <Month>
 CAST(CAST(REVERSE(SUBSTRING(Data, 21, 2)) AS binary(2)) AS smallint) AS StdHour,       -- Local time
 CAST(CAST(REVERSE(SUBSTRING(Data, 23, 2)) AS binary(2)) AS smallint) AS StdMinute,
 CAST(CAST(REVERSE(SUBSTRING(Data, 25, 2)) AS binary(2)) AS smallint) AS StdSecond,
 CAST(CAST(REVERSE(SUBSTRING(Data, 27, 2)) AS binary(2)) AS smallint) AS StdMillisec,
 -- When DST starts:
 CAST(CAST(REVERSE(SUBSTRING(Data, 29, 2)) AS binary(2)) AS smallint) AS DSTYear,       -- See above
 CAST(CAST(REVERSE(SUBSTRING(Data, 31, 2)) AS binary(2)) AS smallint) AS DSTMonth,
 CAST(CAST(REVERSE(SUBSTRING(Data, 33, 2)) AS binary(2)) AS smallint) AS DSTDayOfWeek,
 CAST(CAST(REVERSE(SUBSTRING(Data, 35, 2)) AS binary(2)) AS smallint) AS DSTWeek,
 CAST(CAST(REVERSE(SUBSTRING(Data, 37, 2)) AS binary(2)) AS smallint) AS DSTHour,
 CAST(CAST(REVERSE(SUBSTRING(Data, 39, 2)) AS binary(2)) AS smallint) AS DSTMinute,
 CAST(CAST(REVERSE(SUBSTRING(Data, 41, 2)) AS binary(2)) AS smallint) AS DSTSecond,
 CAST(CAST(REVERSE(SUBSTRING(Data, 43, 2)) AS binary(2)) AS smallint) AS DSTMillisec
FROM #tztable;
DROP TABLE #tztable

一个（复杂的）T-SQL 函数可以使用此数据来确定当前 DST 规则期间所有日期的确切偏移量。

这是为特定的英国应用程序编写的答案，完全基于 SELECT。

1. 没有时区偏移（例如英国）
2. 从 3 月的最后一个星期日开始到 10 月的最后一个星期日结束的夏令时编写（英国规则）

3. 不适用于夏令时开始当天的午夜和凌晨 1 点之间。这可以更正，但为其编写的应用程序不需要它。

   -- A variable holding an example UTC datetime in the UK, try some different values:
   DECLARE
   @App_Date datetime;
   set @App_Date = '20250704 09:00:00'
   
   -- Outputting the local datetime in the UK, allowing for daylight saving:
   SELECT
   case
   when @App_Date >= dateadd(day, 1 - datepart(weekday, dateadd(day, -1, dateadd(month, 3, dateadd(year, datediff(year, 0, @App_Date), 0)))), dateadd(day, -1, dateadd(month, 3, dateadd(year, datediff(year, 0, @App_Date), 0))))
       and @App_Date < dateadd(day, 1 - datepart(weekday, dateadd(day, -1, dateadd(month, 10, dateadd(year, datediff(year, 0, @App_Date), 0)))), dateadd(day, -1, dateadd(month, 10, dateadd(year, datediff(year, 0, @App_Date), 0))))
       then DATEADD(hour, 1, @App_Date) 
   else @App_Date 
   end
   

DECLARE @TimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation', 'TimeZoneKeyName', @TimeZone OUT
SELECT @TimeZone
DECLARE @someUtcTime DATETIME
SET @someUtcTime = '2017-03-05 15:15:15'
DECLARE @TimeBiasAtSomeUtcTime INT
SELECT @TimeBiasAtSomeUtcTime = DATEDIFF(MINUTE, @someUtcTime, @someUtcTime AT TIME ZONE @TimeZone)
SELECT DATEADD(MINUTE, @TimeBiasAtSomeUtcTime * -1, @someUtcTime)