SQL Server 2019中将XML元素本地时间转换为UTC

要将XML列内带时区的timestamp元素转换为UTC格式（如将2024-08-20T10:55:37+10:00转为2024-08-20T00:55:37Z），可以通过提取XML时间值→转换为UTC→替换原XML节点值的流程实现，具体步骤如下：

1. 验证时间转换逻辑 #

先确认原时间转UTC的结果是否符合预期：

SELECT 
    Id,
    -- 提取原时间并转为datetimeoffset类型
    Data.value('(/root/timestamp/text())[1]', 'datetimeoffset') AS OriginalTimestamp,
    -- 转换为UTC并格式化为带Z的ISO8601字符串
    CONVERT(varchar(20), SWITCHOFFSET(Data.value('(/root/timestamp/text())[1]', 'datetimeoffset'), '+00:00'), 127) AS UtcTimestamp
FROM TestTable;

2. 执行XML更新 #

使用UPDATE结合modify方法，将计算好的UTC时间替换原timestamp节点的值：

UPDATE TestTable
SET Data.modify('
    replace value of (/root/timestamp/text())[1]
    with sql:column("Source.UtcTimestamp")
')
FROM (
    SELECT 
        Id,
        CONVERT(varchar(20), SWITCHOFFSET(Data.value('(/root/timestamp/text())[1]', 'datetimeoffset'), '+00:00'), 127) AS UtcTimestamp
    FROM TestTable
) AS Source
WHERE TestTable.Id = Source.Id;

3. 验证更新结果 #

执行查询确认XML中的timestamp已转为UTC格式：

SELECT Id, Data FROM TestTable;

注意事项 #

• 确保XML中的timestamp值是合法的datetimeoffset格式，否则转换会抛出错误
• 若XML结构中有多个timestamp元素，可调整XPath路径（如//timestamp/text()匹配所有节点）
• CONVERT的127格式会自动为UTC时间添加Z后缀，符合目标格式要求

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