SQL Server 2014时区转换求助：无需硬编码及适配夏令时

嘿，我完全懂你的困扰——SQL Server 2014确实没有AT TIME ZONE这个便捷的函数，硬编码时区偏移量不仅死板，还根本没法处理夏令时的自动切换问题。别担心，我给你两个不需要硬编码的实现方案，都是专门适配2014版本的：

方案一：纯SQL自定义时区规则表 #

这种方案不需要依赖任何外部组件，完全用SQL实现，适合对CLR集成有限制的环境。

第一步：创建时区规则表 #

先建立一个存储时区标准偏移、夏令时偏移，以及夏令时起止规则的表：

CREATE TABLE TimeZoneRules (
    TimeZoneName VARCHAR(50) PRIMARY KEY,
    StandardOffset VARCHAR(6) NOT NULL, -- 标准时间偏移
    DaylightOffset VARCHAR(6) NOT NULL, -- 夏令时偏移
    DaylightStartMonth INT NOT NULL,    -- 夏令时开始月份
    DaylightStartWeek INT NOT NULL,     -- 开始月份的第N个星期
    DaylightStartWeekday INT NOT NULL,  -- 开始星期几（1=周日，2=周一...）
    DaylightStartHour INT NOT NULL,     -- 开始时间（小时）
    DaylightEndMonth INT NOT NULL,      -- 夏令时结束月份
    DaylightEndWeek INT NOT NULL,       -- 结束月份的第N个星期
    DaylightEndWeekday INT NOT NULL,    -- 结束星期几
    DaylightEndHour INT NOT NULL        -- 结束时间（小时）
);

第二步：插入目标时区规则 #

比如你需要转换到巴西利亚时区（对应偏移-03:00标准时，夏令时-02:00），就插入对应的规则（注意夏令时规则可能随年份调整，要确认最新规则）：

INSERT INTO TimeZoneRules VALUES (
    'Brasilia Standard Time',
    '-03:00', '-02:00',
    10, 3, 1, 0, -- 十月第三个周日凌晨0点开始夏令时
    2, 3, 1, 0   -- 二月第三个周日凌晨0点结束夏令时
);

第三步：创建判断夏令时的函数 #

写一个自定义函数，根据输入日期和时区名称，判断该日期是否处于夏令时：

CREATE FUNCTION dbo.IsDaylightSavingTime (@InputDate DATETIME, @TimeZoneName VARCHAR(50))
RETURNS BIT
AS
BEGIN
    DECLARE @RuleRow TimeZoneRules;
    SELECT @RuleRow = * FROM TimeZoneRules WHERE TimeZoneName = @TimeZoneName;

    -- 计算当年夏令时开始日期
    DECLARE @DaylightStart DATETIME;
    SET @DaylightStart = DATEADD(HOUR, @RuleRow.DaylightStartHour,
        DATEADD(DAY, (@RuleRow.DaylightStartWeek * 7) - DATEPART(WEEKDAY, DATEFROMPARTS(YEAR(@InputDate), @RuleRow.DaylightStartMonth, 1)) + 1,
            DATEFROMPARTS(YEAR(@InputDate), @RuleRow.DaylightStartMonth, 1)));

    -- 计算当年夏令时结束日期
    DECLARE @DaylightEnd DATETIME;
    SET @DaylightEnd = DATEADD(HOUR, @RuleRow.DaylightEndHour,
        DATEADD(DAY, (@RuleRow.DaylightEndWeek * 7) - DATEPART(WEEKDAY, DATEFROMPARTS(YEAR(@InputDate), @RuleRow.DaylightEndMonth, 1)) + 1,
            DATEFROMPARTS(YEAR(@InputDate), @RuleRow.DaylightEndMonth, 1)));

    -- 判断是否在夏令时区间内
    RETURN CASE WHEN @InputDate >= @DaylightStart AND @InputDate < @DaylightEnd THEN 1 ELSE 0 END;
END;

第四步：动态转换时区 #

现在就可以用这个函数动态获取偏移量，完成时区转换：

SELECT 
    DATETIMEFIELD,
    SWITCHOFFSET(
        TODATETIMEOFFSET(DATETIMEFIELD, (SELECT StandardOffset FROM TimeZoneRules WHERE TimeZoneName = 'Brasilia Standard Time')),
        CASE WHEN dbo.IsDaylightSavingTime(DATETIMEFIELD, 'Brasilia Standard Time') = 1 
             THEN (SELECT DaylightOffset FROM TimeZoneRules WHERE TimeZoneName = 'Brasilia Standard Time')
             ELSE (SELECT StandardOffset FROM TimeZoneRules WHERE TimeZoneName = 'Brasilia Standard Time')
        END
    ) AS LocalTimeWithTimeZone
FROM YourStagingTable;

这个方案的优势是所有逻辑都在SQL层面，不需要额外配置，后续夏令时规则变化时，只需要更新TimeZoneRules表的数据即可，不用修改SQL代码。

方案二：利用CLR函数调用.NET时区功能 #

如果你的SQL Server环境允许启用CLR集成，这个方案会更省心——直接借助.NET自带的TimeZoneInfo类，它已经内置了全球所有时区的夏令时规则，不需要手动维护。

第一步：启用CLR集成 #

先在SQL Server中开启CLR支持：

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'clr enabled', 1;
RECONFIGURE;

第二步：编写C# CLR函数 #

创建一个C#类库项目，写一个静态方法来处理时区转换：

using System;
using Microsoft.SqlServer.Server;

public class TimeZoneConverter
{
    [SqlFunction(DataAccess = DataAccessKind.None)]
    public static DateTime? ConvertToLocalTime(DateTime utcTime, string targetTimeZoneId)
    {
        try
        {
            // 查找目标时区（时区ID可以用.NET的标准ID，比如"Brasilia Standard Time"）
            TimeZoneInfo targetZone = TimeZoneInfo.FindSystemTimeZoneById(targetTimeZoneId);
            // 将UTC时间转换为目标时区时间
            return TimeZoneInfo.ConvertTimeFromUtc(utcTime, targetZone);
        }
        catch (Exception)
        {
            // 处理无效时区或日期的情况，返回NULL
            return null;
        }
    }
}

编译这个项目生成DLL文件。

第三步：注册CLR函数到SQL Server #

在SQL Server中注册刚才生成的DLL，并创建对应的函数：

CREATE ASSEMBLY TimeZoneConverterAssembly
FROM 'C:\Your\DLL\Path\TimeZoneConverter.dll'
WITH PERMISSION_SET = SAFE;

CREATE FUNCTION dbo.ConvertToLocalTime(@UtcTime DATETIME, @TargetTimeZoneId NVARCHAR(100))
RETURNS DATETIME
AS EXTERNAL NAME TimeZoneConverterAssembly.TimeZoneConverter.ConvertToLocalTime;

第四步：调用CLR函数转换时区 #

现在就可以直接调用函数完成转换，夏令时会自动处理：

SELECT DATETIMEFIELD, dbo.ConvertToLocalTime(DATETIMEFIELD, 'Brasilia Standard Time') AS LocalTime
FROM YourStagingTable;

这个方案的优势是不需要手动维护夏令时规则，.NET会自动更新时区数据（只要服务器的.NET框架是最新的），代码更简洁，准确性更高。

内容的提问来源于stack exchange，提问作者Everton Gomes