如何将 Sql Server 2008 DateTimeOffset 转换为 DateTime
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4953903/
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
How can I convert a Sql Server 2008 DateTimeOffset to a DateTime
提问by Pure.Krome
I'm hoping to convert a table which has a DATETIMEOFFSETfield, down to a DATETIMEfield BUT recalculates the time by taking notice of the offset. This, in effect, converts the value to UTC.
我希望将具有DATETIMEOFFSET字段的表转换为字段，DATETIME但通过注意偏移量来重新计算时间。这实际上将值转换为UTC。
eg.
例如。
CreatedOn: 2008-12-19 17:30:09.0000000 +11:00
that will get converted to
这将被转换为
CreatedOn: 2008-12-19 06:30:09.0000000
or
或者
CreatedOn: 2008-12-19 06:30:09.0000000 + 00:00 -- that's a `DATETIMEOFFSET`, but `UTC`.
Cheers :)
干杯:)
回答by RichardTheKiwi
Converting using almost any style will cause the datetime2 value to be converted to UTC.
Also, conversion from datetime2 to datetimeoffset simply sets the offset at +00:00, per the below, so it is a quick way to convert from Datetimeoffset(offset!=0)to Datetimeoffset(+00:00)
使用几乎任何样式进行转换都会导致 datetime2 值转换为 UTC。
此外，从转换到DATETIME2 DATETIMEOFFSET简单地设置在偏移+00:00，按下面的，所以要转换一个快速的方法Datetimeoffset(offset!=0)来Datetimeoffset(+00:00)
declare @createdon datetimeoffset
set @createdon = '2008-12-19 17:30:09.1234567 +11:00'
select CONVERT(datetime2, @createdon, 1)
--Output: 2008-12-19 06:30:09.12
select convert(datetimeoffset,CONVERT(datetime2, @createdon, 1))
--Output: 2008-12-19 06:30:09.1234567 +00:00
回答by Vipeout
I'd use the built in SQL option:
我会使用内置的 SQL 选项：
select SWITCHOFFSET(cast('2008-12-19 17:30:09.0000000 +11:00' as datetimeoffset),'+00:00')
回答by Jeremy
I know this is an old question but, if you want to convert DateTimeOffset to a DateTime, I think you need to take into account the timezone of the server you are converting on. If you just do a CONVERT(datetime, @MyDate, 1) you will simply lose the time zone, which likely results in an incorrect conversion.
我知道这是一个老问题，但是，如果您想将 DateTimeOffset 转换为 DateTime，我认为您需要考虑要转换的服务器的时区。如果你只是做一个 CONVERT(datetime, @MyDate, 1) 你只会丢失时区，这可能会导致错误的转换。
I think you first need to switch the offset of the DateTimeOffset value, then do the conversion.
我认为您首先需要切换DateTimeOffset值的偏移量，然后进行转换。
DECLARE @MyDate DATETIMEOFFSET = '2013-11-21 00:00:00.0000000 -00:00';
SELECT CONVERT(DATETIME, SWITCHOFFSET(@MyDate, DATEPART(tz,SYSDATETIMEOFFSET())));
The result of converting '2013-11-21 00:00:00.0000000 -00:00' to a DateTime on a server who's offset is -7:00 will be 2013-11-20 17:00:00.000. With the above logic it doesn't mater what the time zone of the server or the offset of the DateTime value, it will be converted to DateTime in the servers time zone.
在偏移量为 -7:00 的服务器上将 '2013-11-21 00:00:00.0000000 -00:00' 转换为 DateTime 的结果将是 2013-11-20 17:00:00.000。根据上述逻辑，服务器的时区或 DateTime 值的偏移量无关紧要，它将转换为服务器时区中的 DateTime。
I believe you need to do this because a DateTime value includes an assumption that the value is in the time zone of the server.
我相信您需要这样做，因为 DateTime 值包含一个假设，即该值位于服务器的时区。
回答by Jeremy
Note: The timezone information is discardedin conversion if no style ("126" here) is specified. It might also be discarded in some of the other styles, I don't know -- in any case the following correctly adjusts for the TZ information. See CAST and CONVERT.
注意：如果没有指定样式（此处为“126”），则转换时会丢弃时区信息。它也可能在其他一些样式中被丢弃，我不知道 - 在任何情况下，以下内容都会正确调整 TZ 信息。请参阅CAST 和 CONVERT。
select convert(datetime, cast('2008-12-19 17:30:09.0000000 +11:00' as datetimeoffset), 126) as utc;
Happy SQL'ing.
快乐的 SQL'ing。
Edit
编辑
Not sure if it matters but ... datetimeCan't actually store that level of precision/accuracy. If the above is run the fractional seconds will be truncated to 3 digits (and accuracy is less than that). The same-same with datetime2(and datetimeoffset(7)) produces a non-truncated value:
不确定它是否重要，但是......datetime实际上无法存储这种级别的精度/准确度。如果上面运行，小数秒将被截断为 3 位数（准确度低于此值）。与datetime2(and datetimeoffset(7))相同产生非截断值：
select convert(datetime2, cast('2008-12-19 17:30:09.1234567 +11:00' as datetimeoffset(7)), 126) as utc;
回答by Duane Pfeiffer
In order to account for daylight savings time, I used the following:
为了考虑夏令时，我使用了以下内容：
CONVERT(
DateTime,
SWITCHOFFSET(
CONVERT(
DateTimeOffset,
CONVERT(
DateTime,
[time_stamp_end_of_interval],
120
)
),
DATENAME(
TzOffset,
CONVERT(
DateTime,
[time_stamp_end_of_interval],
120
) AT TIME ZONE 'Pacific Standard Time'
)
)
)
AS GOOD_PST
Note: time_stamp_end_of_intervalis a varchar
注意：time_stamp_end_of_interval是一个varchar
