如何将 Sql Server 2008 DateTimeOffset 转换为 DateTime

发布于 2024-10-16 21:21:14 字数 421 浏览 9 评论 0原文

我希望将具有 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`.

干杯:)

原文

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.

eg.

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

that will get converted to

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

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

Cheers :)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

奢欲 2024-10-23 21:21:14

使用几乎任何样式进行转换都会导致 datetime2 值转换为 UTC。
此外，从 datetime2 到 datetimeoffset 的转换只需将偏移量设置为 +00:00，如下所示，因此这是从 Datetimeoffset(offset!=0) 转换的快速方法。 > 到日期时间偏移(+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

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

回复收藏 0 原文

寄离 2024-10-23 21:21:14

我会使用内置的 SQL 选项：

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

I'd use the built in SQL option:

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

回复收藏 0 原文

笑看君怀她人 2024-10-23 21:21:14

我知道这是一个老问题，但是，如果您想将 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())));

在偏移量为 -7:00 的服务器上将“2013-11-21 00:00:00.0000000 -00:00”转换为 DateTime 的结果将是 2013-11-20 17:00:00.000。通过上述逻辑，无论服务器的时区或 DateTime 值的偏移量如何，它都会转换为服务器时区的 DateTime。

我相信您需要这样做，因为 DateTime 值包含一个假设，即该值位于服务器的时区中。

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.

I think you first need to switch the offset of the DateTimeOffset value, then do the conversion.

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.

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.

回复收藏 0 原文

若相惜即相离 2024-10-23 21:21:14

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

SQL Server 2016 (13.x) 及更高版本

示例

选择 GETUTCDATE()
选择转换(DATETIME, GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE '中欧标准时间')
选择转换(DATETIME, GETUTCDATE() AT 时区 'UTC' AT 时区 '印度标准时间')

结果将是

<上一页><代码>2020-08-18 08:22:21.640
2020-08-18 10:22:21.640
2020-08-18 13:52:21.640

获取时区名称、偏移量和 DST 标志的完整列表：

SELECT * FROM sys.time_zone_info

DateTimeoffset (Timezone) conversion in SQL Server.

SQL Server 2016 (13.x) and later

Example

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')

Result will be

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

Get a full list of timezone names, offsets and DST flags:

SELECT * FROM sys.time_zone_info

回复收藏 0 原文

南风起 2024-10-23 21:21:14

注意：如果未指定样式（此处为“126”），则转换时时区信息将被丢弃。它也可能在其他一些样式中被丢弃，我不知道——无论如何，以下内容都会正确调整 TZ 信息。请参阅CAST 和 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;

Note: The timezone information is discarded in 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.

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

Happy SQL'ing.

Edit

Not sure if it matters but ... datetime Can'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:

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

回复收藏 0 原文

彻夜缠绵 2024-10-23 21:21:14

从 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

Several ways of converting from DateTimeOffset to DateTime2 (UTC or local).

On 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

回复收藏 0 原文

风筝在阴天搁浅。 2024-10-23 21:21:14

为了考虑夏令时，我使用了以下内容：

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

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_interval is a varchar

回复收藏 0 原文

~没有更多了~