问题描述
我有一个旧表，其中几行有一个日期时间列.我想将其切换到 datetimeoffset 但我希望能够传输已经存在的数据.所以我正在做类似的事情:
I have an old table with a few rows that has a datetime column. I want to switch this to datetimeoffset but I want to be able to transfer the data that already exists. So I'm doing something like:
SET IDENTITY_INSERT Table_Temp ON
INSERT INTO Table_Temp
(Col0, ... ColN,)
SELECT
COl0,.... ColN, from
Table_Original;
SET IDENTITY_INSERT Table_Temp OFF
这有效，但是当我执行 datetime 到 datetimeoffset 分配时，偏移量设置为 0.幸运的是，我想设置的偏移量是当前系统的偏移量.我似乎想不出一个简单的方法来做到这一点.
This works but the offset set is 0 when I do the datetime to datetimeoffset assignment. Fortunately the offset that I want to set it to is the offset of the current system. I can't seem to figure out an easy way to do this.
我希望能够在转换中设置偏移量.我打算使用 C# 实用程序(或 PowerShell)，但我宁愿保持简单.
I want to be able to set the offset within the conversion. I was going to resort to doing a C# utility(or PowerShell) but I would rather keep it simple.
推荐答案
如果您使用的 SQL Server 版本知道 datetimeoffset 类型，则此语法将用于获取服务器的本地 tz 偏移量:
If you're using a version of SQL Server that knows of the datetimeoffset type, this syntax will work for getting you the local tz offset of the server:
选择日期部分(tz,sysdatetimeoffset())
结果在 MINUTES 内.
The result is in MINUTES.
这篇关于将 SQL Server DateTime 列迁移到 DateTimeOffset的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！