SQL Server中如何将datetime与bigint列转换为datetimeoffset类型？

核心转换逻辑 #

首先明确DateTime.Ticks的换算规则：1 Tick = 100纳秒，1分钟 = 60000000000 Ticks，1小时 = 3600000000000 Ticks。我们需要先把Ticks偏移量转换成SQL Server能识别的时区偏移格式，再和原datetime列合并。

方法1：使用SWITCHOFFSET函数（推荐） #

这是最高效的实现方式，直接将Ticks转为分钟偏移量，再通过SWITCHOFFSET函数生成目标datetimeoffset：

UPDATE @myTable
SET @dateTimeWithTimeZoneColumn = 
    SWITCHOFFSET(
        CONVERT(datetimeoffset, @dateTimeColumn),
        @timezoneOffsetColumn / 600000000 -- 将Ticks转换为分钟偏移量
    )

说明：

• @timezoneOffsetColumn / 600000000 将Ticks值转为分钟数（示例中108000000000 / 600000000 = 180，对应+03:00）
• SWITCHOFFSET会把原datetime转换后的datetimeoffset切换到指定偏移，直接得到目标结果。

方法2：构造字符串后转换 #

如果需要更直观的格式控制，可以手动拼接datetime和偏移字符串，再转换为datetimeoffset：

UPDATE @myTable
SET @dateTimeWithTimeZoneColumn = 
    CONVERT(datetimeoffset, 
        -- 格式化datetime为带毫秒的标准字符串
        CONVERT(varchar(23), @dateTimeColumn, 121) + 
        -- 生成偏移的正负号
        CASE WHEN @timezoneOffsetColumn >= 0 THEN '+' ELSE '-' END + 
        -- 计算偏移小时并补零
        RIGHT('0' + CAST(ABS(@timezoneOffsetColumn / 36000000000) AS varchar(2)), 2) + ':' + 
        -- 计算偏移分钟并补零
        RIGHT('0' + CAST(ABS((@timezoneOffsetColumn % 36000000000) / 600000000) AS varchar(2)), 2)
    )

验证示例：
对于输入2024-09-10 21:00:00.000和108000000000，两种方法都会输出2024-09-10 21:00:00.000 +03:00，完全符合预期。

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