如何将 Sql Server 2008 DateTimeOffset 转换为 DateTime

本文介绍了如何将 Sql Server 2008 DateTimeOffset 转换为 DateTime的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我希望将具有 DATETIMEOFFSET 字段的表转换为 DATETIME 字段，但通过注意偏移量来重新计算时间.这实际上将值转换为 UTC.

I'm hoping to convert a table which has a DATETIMEOFFSET field, down to a DATETIME field BUT recalculates the time by taking notice of the offset. This, in effect, converts the value to UTC.

例如

CreatedOn: 2008-12-19 17:30:09.0000000 +11:00

将被转换为

CreatedOn: 2008-12-19 06:30:09.0000000

或

CreatedOn: 2008-12-19 06:30:09.0000000 + 00:00 -- that's a `DATETIMEOFFSET`, but `UTC`.

干杯:)

推荐答案

使用几乎任何样式的转换都会导致 datetime2 值转换为 UTC.此外，从 datetime2 到 datetimeoffset 的转换只是将偏移量设置为 +00:00，如下所示，因此这是一种从 Datetimeoffset(offset!=0) 到 Datetimeoffset(+00:00)

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)

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