仅更新SQL DATETIME列中的时区偏移量相关问题咨询

报错原因 #

你原有写法的问题来自两点：

1. DATEDIFF(DAY, 0, Date)中的0会被隐式转换为datetime类型，和datetimeoffset类型的Date字段运算时会触发类型不匹配，时区部分无法被识别，就会抛出转换失败的报错
2. DATEADD针对datetime类型仅支持最高毫秒级精度，无法保留你需要的纳秒级小数，也不能保留时区偏移属性

正确实现方案 #

以下方案均针对SQL Server（从报错信息判断），可完整保留所有时间精度：

方案1：仅修改时区偏移，保留原有日期、时间部分完全不变

符合你提到的「所有数据时间部分固定为23:59:59.0000000仅修改时区」的场景，时间和日期部分完全不变，仅替换末尾的时区偏移：

UPDATE 你的表名
SET [Date] = TODATETIMEOFFSET(CAST([Date] AS datetime2(7)), '+01:00')

逻辑说明：先把原datetimeoffset类型的字段转为不带时区的datetime2(7)，完整保留年/月/日和纳秒级时间精度，再给它附加上+01:00的时区偏移，无任何精度损失。

方案2：保留原时间对应的UTC时刻，仅调整时区显示

如果你需要转换后时间对应的UTC时间点和原来完全一致，只是用+01:00时区做展示，用SWITCHOFFSET函数即可：

UPDATE 你的表名
SET [Date] = SWITCHOFFSET([Date], '+01:00')

转换效果示例：原时间2023-11-30 23:59:59.0000000 +00:00会转换为2023-12-01 00:59:59.0000000 +01:00，二者对应同一个UTC时刻，精度完全保留。

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