将 SQL Server DateTime 列迁移到 DateTimeOffset

2021-09-10 00:00:00 sql tsql sql-server-2008 sql-server datetimeoffset

我有一个旧表，其中几行有一个日期时间列.我想将其切换到 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.

相关文章