将 SQL Server DateTime 列迁移到 DateTimeOffset
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2008522/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Migrate SQL Server DateTime column to DateTimeOffset
提问by hdz
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:
我有一个旧表，其中几行有一个日期时间列。我想将其切换为 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
This works but the offset set is 0 when i do the dattime to datetimeoffset assignment. Fortunately the offset that i want to set it to is the offset of the current system. I'm no tsql guru but i can't seem to figure out an easy way to do this.
这有效，但是当我执行 dattime 到 datetimeoffset 分配时，偏移量设置为 0。幸运的是，我想设置的偏移量是当前系统的偏移量。我不是 tsql 大师，但我似乎无法找到一种简单的方法来做到这一点。
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.
我希望能够在转换中设置偏移量。我打算使用 ac# 实用程序（或 PowerShell），但我宁愿保持简单。
采纳答案by Broam
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:
如果您使用的 SQL Server 版本知道 datetimeoffset 类型，则此语法将用于获取服务器的本地 tz 偏移量：
select datepart(tz,sysdatetimeoffset())
select datepart(tz,sysdatetimeoffset())
The result is in MINUTES.
结果以分钟为单位。
回答by Hogan
See below for doc, you probably want something like:
请参阅下面的文档，您可能需要以下内容：
-- up here set the @time_zone variable.
INSERT INTO Table_Temp
(Col0, ... ColN,)
SELECT
COl0, TODATETIMEOFFSET(COLDATE, @time_zone),.... ColN, from
Table_Original;
From MSDN
来自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');
SWITCHOFFSET 函数将输入 DATETIMEOFFSET 值调整为指定的时区，同时保留 UTC 值。语法是 SWITCHOFFSET(datetimeoffset_value, time_zone)。例如，以下代码将当前系统 datetimeoffset 值调整为时区 GMT +05:00：
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-05:00');
因此，如果当前系统日期时间偏移值为 2009 年 2 月 12 日 10:00:00.0000000 -08:00，则此代码返回值 2009 年 2 月 12 日 13:00:00.0000000 -05:00。
TODATETIMEOFFSET 函数设置输入日期和时间值的时区偏移量。它的语法是 TODATETIMEOFFSET(date_and_time_value, time_zone)。
此函数在几个方面与 SWITCHOFFSET 不同。首先，它不限于作为输入的 datetimeoffset 值；相反，它接受任何日期和时间数据类型。其次，它不会尝试根据源值和指定时区之间的时区差异来调整时间，而是简单地返回具有指定时区的输入日期和时间值作为 datetimeoffset 值。
TODATETIMEOFFSET 函数的主要目的是通过给定的时区偏移量将不知道时区的类型转换为 DATETIMEOFFSET。如果给定的日期和时间值是 DATETIMEOFFSET，则 TODATETIMEOFFSET 函数会根据相同的原始本地日期和时间值加上新的给定时区偏移量更改 DATETIMEOFFSET 值。
例如，当前系统 datetimeoffset 值为 2009 年 2 月 12 日 10:00:00.0000000 -08:00，您运行以下代码：
选择 TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '-05:00');
返回值 2009 年 2 月 12 日 10:00:00.0000000 -05:00。请记住 SWITCHOFFSET 函数返回 2009 年 2 月 12 日 13:00:00.0000000 -05:00 因为它根据输入 (-08:00) 和指定时区 (-05:00) 之间的时区差异调整时间.
如前所述，您可以将 TODATETIMEOFFSET 函数与任何日期和时间数据类型一起用作输入。例如，以下代码采用当前系统日期和时间值并将其作为时区为 -00:05 的 datetimeoffset 值返回：
选择 TODATETIMEOFFSET(SYSDATETIME(), '-05:00');
回答by Softlion
These conversions functions will not work correctly if DST saving is active in the target timezone, as the timezone offset changes inside the same year.
如果 DST 保存在目标时区处于活动状态，这些转换函数将无法正常工作，因为时区偏移量在同一年内发生了变化。
回答by Andrew
You can figure out what the offset of the current SQL server is using the following.
您可以使用以下内容找出当前 SQL 服务器的偏移量。
select datediff(MI,getdate(), getutcdate())
You need to get the offset in minutes and not hours since there are a number of half hour and even a quarter hour time zone.
您需要在几分钟而不是几小时内获得偏移量，因为有许多半小时甚至一刻钟的时区。
Using the minutes value, you can alter your values going in (assuming they were historically all recorded as local time) by using something like
使用分钟值，您可以通过使用类似
select dateadd(mi,datediff(MI,getdate(), getutcdate()), yourDateField)
For efficiency I would calculate it once into a variable and use that, since the difference is not going to change.
为了提高效率，我会将它计算一次到一个变量中并使用它，因为差异不会改变。
回答by Mihail Shishkov
For anyone trying to solve this problem correctly, accounting for DST here is the tool to do it.
对于任何试图正确解决这个问题的人来说，在这里考虑 DST 是解决这个问题的工具。
回答by Tyler Hains
This is a slight variation of an answer already provided, and does NOT account for DST changes. However, it might be good enough for many purposes:
这是已经提供的答案的轻微变化，并且不考虑 DST 更改。但是，对于许多目的来说，它可能已经足够了：
dateadd(minute, -datepart(tz, sysdatetimeoffset()), @legacyDatetime)
