寻求SQL Server 2014及更早版本中AT TIME ZONE的替代方案

I feel your pain—being stuck on SQL Server 2014 because Windows 7 won’t let you upgrade to 2016 is frustrating, especially when scripts rely on AT TIME ZONE which only landed in 2016. Let’s break down a few solid alternatives that replicate its core functionality, including handling daylight saving time (DST) since that’s one of the key benefits of the native function.

Option 1: Fixed Offset Conversion (No DST Handling) #

If you’re working with time zones that don’t observe DST, or you don’t need to account for it, this is the simplest approach. Just use DATEADD to adjust the time by the fixed offset from UTC.

Example: Convert UTC to Eastern Standard Time (fixed -5 hours):

SELECT
    GETUTCDATE() AS UTC_Time,
    DATEADD(HOUR, -5, GETUTCDATE()) AS Eastern_Time_Fixed;

Option 2: Custom Function with DST Support #

To match AT TIME ZONE’s ability to auto-adjust for DST, you can build a function that calculates DST start/end dates for a given time zone and adjusts the offset accordingly. Here’s a reusable function for most common time zones:

CREATE FUNCTION dbo.ConvertTimeZone_WithDST
(
    @InputDateTime DATETIME,
    @SourceUTCOffset INT, -- Offset of input time from UTC (e.g., UTC = 0, EST = -5)
    @TargetStandardOffset INT, -- Target zone's non-DST offset
    @DSTStartMonth INT, -- Month DST starts (1=Jan, 12=Dec)
    @DSTStartWeek INT, -- Week of the month DST starts (e.g., 2 = 2nd week)
    @DSTStartWeekday INT, -- Day of week DST starts (1=Sunday, 7=Saturday)
    @DSTEndMonth INT, -- Month DST ends
    @DSTEndWeek INT, -- Week of the month DST ends
    @DSTEndWeekday INT -- Day of week DST ends
)
RETURNS DATETIME
AS
BEGIN
    DECLARE @DSTStart DATETIME, @DSTEnd DATETIME;

    -- Calculate DST start date (e.g., 2nd Sunday in March at 2AM for US Eastern)
    SET @DSTStart = DATEADD(HOUR, 2, DATEADD(WEEK, @DSTStartWeek-1, 
        DATEADD(DAY, 1-@DSTStartWeekday, 
        DATEADD(MONTH, @DSTStartMonth-1, DATEADD(YEAR, YEAR(@InputDateTime)-1900, 0)))));

    -- Calculate DST end date (e.g., 1st Sunday in November at 2AM for US Eastern)
    SET @DSTEnd = DATEADD(HOUR, 2, DATEADD(WEEK, @DSTEndWeek-1, 
        DATEADD(DAY, 1-@DSTEndWeekday, 
        DATEADD(MONTH, @DSTEndMonth-1, DATEADD(YEAR, YEAR(@InputDateTime)-1900, 0)))));

    -- Adjust target offset if input time is in DST window
    IF @InputDateTime BETWEEN @DSTStart AND @DSTEnd
        SET @TargetStandardOffset = @TargetStandardOffset + 1;

    -- Convert: first to UTC, then to target zone
    RETURN DATEADD(HOUR, @TargetStandardOffset - @SourceUTCOffset, @InputDateTime);
END
GO

How to Use It #

Convert UTC to US Eastern Time (which uses DST):

SELECT
    GETUTCDATE() AS UTC_Time,
    dbo.ConvertTimeZone_WithDST(GETUTCDATE(), 0, -5, 3, 2, 1, 11, 1, 1) AS Eastern_Time_WithDST;

Option 3: Maintain a Time Zone Table (Scalable for Multiple Zones) #

If you need to work with multiple time zones, create a table to store DST rules for each zone, then reference it in your conversion function. This makes updates easier if DST rules change.

-- Create time zone metadata table
CREATE TABLE dbo.TimeZoneRules (
    TimeZoneName VARCHAR(100) PRIMARY KEY,
    StandardUTCOffset INT,
    DSTUTCOffset INT,
    DSTStartMonth INT,
    DSTStartWeek INT,
    DSTStartWeekday INT,
    DSTEndMonth INT,
    DSTEndWeek INT,
    DSTEndWeekday INT
);

-- Insert common time zones
INSERT INTO dbo.TimeZoneRules VALUES
('UTC', 0, 0, NULL, NULL, NULL, NULL, NULL, NULL),
('Eastern Standard Time', -5, -4, 3, 2, 1, 11, 1, 1),
('Central Standard Time', -6, -5, 3, 2, 1, 11, 1, 1);

-- Build a function that uses the table
CREATE FUNCTION dbo.ConvertTimeZone_FromTable
(
    @InputDateTime DATETIME,
    @SourceTimeZone VARCHAR(100),
    @TargetTimeZone VARCHAR(100)
)
RETURNS DATETIME
AS
BEGIN
    DECLARE @SourceOffset INT, @TargetOffset INT;
    DECLARE @DstStartMonth INT, @DstStartWeek INT, @DstStartWeekday INT;
    DECLARE @DstEndMonth INT, @DstEndWeek INT, @DstEndWeekday INT;

    -- Get source zone offset
    SELECT @SourceOffset = StandardUTCOffset FROM dbo.TimeZoneRules WHERE TimeZoneName = @SourceTimeZone;

    -- Get target zone details
    SELECT
        @TargetOffset = StandardUTCOffset,
        @DstStartMonth = DSTStartMonth,
        @DstStartWeek = DSTStartWeek,
        @DstStartWeekday = DSTStartWeekday,
        @DstEndMonth = DSTEndMonth,
        @DstEndWeek = DSTEndWeek,
        @DstEndWeekday = DSTEndWeekday
    FROM dbo.TimeZoneRules WHERE TimeZoneName = @TargetTimeZone;

    -- If target zone doesn't use DST, skip adjustment
    IF @DstStartMonth IS NULL
        RETURN DATEADD(HOUR, @TargetOffset - @SourceOffset, @InputDateTime);

    -- Otherwise, use the DST function
    RETURN dbo.ConvertTimeZone_WithDST(@InputDateTime, @SourceOffset, @TargetOffset, 
        @DstStartMonth, @DstStartWeek, @DstStartWeekday, @DstEndMonth, @DstEndWeek, @DstEndWeekday);
END
GO

Example Usage #

SELECT
    GETUTCDATE() AS UTC_Time,
    dbo.ConvertTimeZone_FromTable(GETUTCDATE(), 'UTC', 'Eastern Standard Time') AS Eastern_Time,
    dbo.ConvertTimeZone_FromTable(GETUTCDATE(), 'UTC', 'Central Standard Time') AS Central_Time;

Important Notes #

• DST rules vary by region and can change over time—make sure to update your table/function if rules shift.
• These functions use DATETIME; if you’re using DATETIME2, adjust the function parameters accordingly (they’re compatible with minimal changes).
• For time zones with non-hour offsets (e.g., India’s UTC+5:30), use DATEADD(MINUTE, offset_in_minutes, ...) instead of hours.

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