如何将 Sql Server 2008 的 DateTimeOffset 转换为 DateTime

Question

我希望将一个包含DATETIMEOFFSET字段的表格转换为一个DATETIME字段，但是要注意偏移量并重新计算时间。这会将值转换为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`.

干杯 :)

- Pure.Krome

---

使用SQL查询将DateTime值从一个时区转换为UTC时区。 - afruzan

Answer 1

使用几乎任何样式进行转换都会导致将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

- RichardTheKiwi

---

请问CONVERT函数中的最后一个参数"1"是什么意思？所有关于该参数的示例都使用字符类型作为输入或输出类型。在这里，我们将datetimeoffset转换为datetime。 - Robo Burned

convert() 函数带有第三个参数，用于指定输出的格式。https://www.w3schools.com/sql/func_convert.asp。其中 1 = "mm/dd/yy" 格式。 - Eric Harlan

第三个参数对于从datetimeoffset转换为datetime2并不真正有任何意义。它是用于varchar转换的。https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql（还有W3Schools？现在有很多更好的选择！） - brianary

我猜第三个参数应该在外部转换中（只是为了以可读的方式打印结果datetimeoffset）。它应该像这样 "select convert(datetimeoffset,CONVERT(datetime2, @createdon), 1)"。 - Thanasis Ioannidis

@OzBob，你可能误解了问题，其中两个选项之一是要删除偏移量。关键是将时间转换为UTC。无论它是否保持+00.00都不重要。 - RichardTheKiwi

请注意下面来自@user166390的回答："如果没有指定样式，转换时将丢弃时区信息"。如果不指定第三个参数，无论是1还是其他什么值，您都会失去正确的UTC转换。 - zola25

Answer 2

我会使用内置的SQL选项：

select SWITCHOFFSET(cast('2008-12-19 17:30:09.0000000 +11:00' as datetimeoffset),'+00:00')

- Vipeout

---

这是最好的答案（也支持SQL Server 2014）。使用内置函数SWITCHOFFSET(...,0)来转换datetimeoffset简单而干净。 - afruzan

这似乎也支持夏令时，这很好。 - Mark Clancy

Answer 3

我知道这是一个老问题，但是如果你想把DateTimeOffset转换成DateTime，我认为你需要考虑正在转换的服务器的时区。如果你只执行CONVERT(datetime, @MyDate, 1)，那么你会简单地丢失时区，而这可能导致不正确的转换。

我认为你首先需要切换DateTimeOffset值的偏移量，然后再进行转换。

DECLARE @MyDate DATETIMEOFFSET = '2013-11-21 00:00:00.0000000 -00:00';
SELECT CONVERT(DATETIME, SWITCHOFFSET(@MyDate, DATEPART(tz,SYSDATETIMEOFFSET())));

将 '2013-11-21 00:00:00.0000000 -00:00' 转换为 DateTime 类型后，如果服务器时区的偏移量为 -7:00，则结果将是 2013-11-20 17:00:00.000。按照上述逻辑，无论服务器的时区或 DateTime 值的偏移量如何，它都将被转换为服务器所在的时区的 DateTime。

我认为你需要这样做，因为 DateTime 值包含一个假设，即该值处于服务器的时区。

- Jeremy

---

谢谢这个 - 我们现在在Azure中运行了更多的代码，但是查询本地数据库，很高兴能够在云中使用DateTimeOffset，通常是UTC，然后将其转换为本地时间，而不必知道本地服务器时区。在云中使用DateTimeOffeset还可以使我们免受云不是UTC的影响。 - Rick Glos

@Jeremy 看起来你没有测试过在你之前5年的答案中的代码。你不需要考虑在转换时所在服务器的时区。无论在哪个时区，0600+04:00 总是转换为 0200（UTC）。 - RichardTheKiwi

一个日期时间值是由你如何解释它而定的。它没有任何时区假设，这就是为什么存在datetimeoffset的原因。 - RichardTheKiwi

@RichardTheKiwi 我认为你没有理解他在做什么。 假设 @MyDate 是外部数据。 假设 DB 将该值存储为具有服务器本地时间的 datetime (这是一种非常常见的应用程序规则，适用于只在一个时区中运行的应用程序)。 在将其从 datetimeoffset 转换之前，您需要更正时区。 SELECT CONVERT(DATETIME, @MyDate) 无论服务器的时区如何以及 datetimeoffset 中指定的偏移量如何，都会返回2013年11月21日午夜，它只是 丢弃了偏移量 而不转换为本地时间。 - Bacon Bits

Answer 4

SQL Server中的DateTimeoffset（时区）转换。

适用于SQL Server 2016（13.x）及更高版本

示例：

Select GETUTCDATE()
Select Convert(DATETIME, GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time')
Select Convert(DATETIME, GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'India Standard Time')

结果将会是：

2020-08-18 08:22:21.640
2020-08-18 10:22:21.640
2020-08-18 13:52:21.640

- Sukesh Chand

---

这就是我一直在寻找的。我需要将时区偏移量保留到最终的日期时间值中，而不是被丢弃。另一个答案中的类型126返回了一个错误。谢谢！ - Jeremy

这正是我所需要的，我需要将时间转换为主时间块，这应该是最高投票答案！！ - Sauron

看起来这似乎没有考虑夏令时。 - Roel

在尝试了其他9个以上的示例并理解了datetimeoffset日期类型之后，终于发现这个方法可行，并且完全符合我的需求 - 真是太棒了！ - undefined

Answer 5

注意：如果没有指定样式（这里是“126”），则在转换中会丢弃时区信息。在某些其他样式中也可能会丢弃它，我不确定——无论如何，以下内容可以正确地调整TZ信息。请参见CAST and CONVERT。

select convert(datetime, cast('2008-12-19 17:30:09.0000000 +11:00' as datetimeoffset), 126) as utc;

祝SQL愉快。

编辑

不确定是否重要，但是...datetime无法存储那个级别的精度/准确性。如果运行上述代码，则小数秒将被截断为3位数字（精度低于此）。相同的情况也适用于datetime2（和datetimeoffset(7)），产生一个非截断值：

select convert(datetime2, cast('2008-12-19 17:30:09.1234567 +11:00' as datetimeoffset(7)), 126) as utc;

- user166390

---

什么是样式126？为什么是126？ - Pure.Krome

@Pure.Krome 请查看回复中的CAST和CONVERT链接。我选择了126，因为我喜欢ISO 8601并且必须选择一个。这与cyberwiki选择1没有什么不同。正如两个答案中所指出的那样，某些样式可能不考虑TZ。 - user166390

在 SQL Server 2008 R2 中进行的快速测试表明，仅使用样式 0 或（等效地）省略样式代码会丢弃时区信息。MSDN 文档中列出的任何其他代码都将保留它。 - Dan J

我发现尝试使用样式126将导致错误（在SQL Server 2012中），因为将DATETIMEOFFSET直接转换为DATETIME2时，该样式不受支持。我没有发现除0和1之外的其他可接受的样式（但我没有尝试它们所有）。使用样式126将DATETIMEOFFSET转换为VARCHAR是有效的。 - Rob Parker

Answer 6

有几种将 DateTimeOffset 转换为 DateTime2（UTC 或本地时间）的方法。

在 SQL Server 2019 上：

DECLARE @dto datetimeoffset = SYSDATETIMEOFFSET();

SELECT @dto as MyDateTimeOffset_EST                                -- 2023-05-24 15:04:59.1321648 -04:00
    ,@dto AT TIME ZONE 'UTC'     as DateTimeOffset_UTC             -- 2023-05-24 19:04:59.1321648 +00:00
    ,SWITCHOFFSET(@dto, 0)       as DateTimeOffset_UTC_also        -- 2023-05-24 19:04:59.1321648 +00:00
    ,CONVERT(datetime2, @dto AT TIME ZONE 'UTC') as DateTime2_Utc  -- 2023-05-24 19:04:59.1321648
    ,CONVERT(datetime2, @dto, 1) as DateTime2_Utc_also             -- 2023-05-24 19:04:59.1321648
    ,CONVERT(datetime2, @dto)    as DateTime2_Local                -- 2023-05-24 15:04:59.1321648
    -- If you want to change to a different time zone:
    ,@dto AT TIME ZONE 'UTC'
       AT TIME ZONE 'Pacific Standard Time' as DateTimeOffset_PST  -- 2023-05-24 12:04:59.1321648 -07:00

- bouvierr

Answer 7

为了考虑夏令时，我使用了以下代码：

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

注意： time_stamp_end_of_interval 是一个 varchar。

- Duane Pfeiffer

---

只使用 SWITCHOFFSET(..., 0) 转换 datetimeoffset 到 UTC 时，与夏令时相关的问题便不会存在。请参考 https://learn.microsoft.com/en-us/sql/t-sql/functions/switchoffset-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15#arguments。 - afruzan