sql - 将 SQL Server DateTime 列迁移到 DateTimeOffset

Question

我有一个旧表，其中有几行，其中有一个日期时间列。我想将其切换为datetimeoffset，但我希望能够传输已存在的数据。所以我正在做类似的事情:

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。幸运的是，我想要将其设置为当前系统的偏移量。我似乎无法找到一个简单的方法来做到这一点。

我希望能够在转换中设置偏移量。我本来打算使用 C# 实用程序(或 PowerShell)，但我宁愿保持简单。

Answer 1

请参阅下面的文档，您可能需要类似的内容:

-- up here set the @time_zone variable.

INSERT INTO Table_Temp
    (Col0, ... ColN,)
SELECT 
    COl0, TODATETIMEOFFSET(COLDATE, @time_zone),.... ColN, from 
Table_Original;

来自MSDN

The SWITCHOFFSET function adjusts an input DATETIMEOFFSET value to a specified time zone, while preserving the UTC value. The syntax is SWITCHOFFSET(datetimeoffset_value, time_zone). For example, the following code adjusts the current system datetimeoffset value to time zone GMT +05:00:

SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-05:00');

So if the current system datetimeoffset value is February 12, 2009 10:00:00.0000000 -08:00, this code returns the value February 12, 2009 13:00:00.0000000 -05:00.

The TODATETIMEOFFSET function sets the time zone offset of an input date and time value. Its syntax is TODATETIMEOFFSET(date_and_time_value, time_zone).

This function is different from SWITCHOFFSET in several ways. First, it is not restricted to a datetimeoffset value as input; rather it accepts any date and time data type. Second, it does not try to adjust the time based on the time zone difference between the source value and the specified time zone but instead simply returns the input date and time value with the specified time zone as a datetimeoffset value.

The main purpose of the TODATETIMEOFFSET function is to convert types that are not time zone aware to DATETIMEOFFSET by the given time zone offset. If the given date and time value is a DATETIMEOFFSET, the TODATETIMEOFFSET function changes the DATETIMEOFFSET value based on the same original local date and time value plus the new given time zone offset.

For example, the current system datetimeoffset value is February 12, 2009 10:00:00.0000000 -08:00, and you run the following code:

SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '-05:00');

The value February 12, 2009 10:00:00.0000000 -05:00 is returned. Remember that the SWITCHOFFSET function returned February 12, 2009 13:00:00.0000000 -05:00 because it adjusted the time based on the time zone differences between the input (-08:00) and the specified time zone (-05:00).

As mentioned earlier, you can use the TODATETIMEOFFSET function with any date and time data type as input. For example, the following code takes the current system date and time value and returns it as a datetimeoffset value with a time zone -00:05:

SELECT TODATETIMEOFFSET(SYSDATETIME(), '-05:00');