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

问题描述

如何将 SQL Server datetime 值转换为 datetimeoffset 值?

例如，现有表包含 datetime 值，这些值都是本地" 服务器时间.

从 AuditLog 中选择 TOP 5 ChangeDate改变日期==========================2013-07-25 04:00:03.0602013-07-24 04:00:03.0732013-07-23 04:00:03.2732013-07-20 04:00:02.8702013-07-19 04:00:03.780

我的服务器(碰巧)(现在，今天)比 UTC 晚 4 小时(现在，在美国东部时区，夏令时有效):

SELECT SYSDATETIMEOFFSET()2013-07-25 14:42:41.6450840 -04:00

我想将存储的 datetime 值转换为 datetimeoffset 值；使用服务器的当前时区偏移信息.

我渴望的价值观是:

ChangeDate ChangeDateOffset==========================================================2013-07-25 04:00:03.060 2013-07-25 04:00:03.0600000 -04:002013-07-24 04:00:03.073 2013-07-24 04:00:03.0730000 -04:002013-07-23 04:00:03.273 2013-07-23 04:00:03.2730000 -04:002013-07-20 04:00:02.870 2013-07-20 04:00:02.8700000 -04:002013-07-19 04:00:03.780 2013-07-19 04:00:03.7800000 -04:00

您可以看到理想的特征:

2013-07-19 04:00:03.7800000 -04:00\_________________________/\____/||本地"datetime 与 UTC 的偏移量

但实际值是:

选择 TOP 5改变日期，CAST(ChangeDate AS datetimeoffset) AS ChangeDateOffset来自审核日志更改日期更改日期偏移==========================================================2013-07-25 04:00:03.060 2013-07-25 04:00:03.0600000 +00:002013-07-24 04:00:03.073 2013-07-24 04:00:03.0730000 +00:002013-07-23 04:00:03.273 2013-07-23 04:00:03.2730000 +00:002013-07-20 04:00:02.870 2013-07-20 04:00:02.8700000 +00:002013-07-19 04:00:03.780 2013-07-19 04:00:03.7800000 +00:00

具有无效特征:

2013-07-19 04:00:03.7800000 +00:00\_________________________/\____/^|不存在与 UTC 的偏移

所以我随机尝试其他的东西:

选择 TOP 5改变日期，CAST(ChangeDate AS datetimeoffset) AS ChangeDateOffset,DATEADD(分钟, DATEDIFF(分钟, GETDATE(), GETUTCDATE()), ChangeDate) AS ChangeDateUTC,CAST(DATEADD(minute, DATEDIFF(minute, GETDATE(), GETUTCDATE()), ChangeDate) AS datetimeoffset) AS ChangeDateUTCOffset,SWITCHOFFSET(CAST(ChangeDate AS datetimeoffset), DATEDIFF(minute, GETUTCDATE(), GETDATE())) AS ChangeDateSwitchedOffset来自审核日志ORDER BY ChangeDate DESC

有结果:

ChangeDate ChangeDateOffset ChangeDateUTC ChangeDateUTCOffset ChangeDateSwitchedOffset=======================================================================================================================================================2013-07-25 04:00:03.060 2013-07-25 04:00:03.0600000 +00:00 2013-07-25 08:00:03.060 2013-07-25 08:00:03.0600000 +00:00 2013-07-25 00:00:03.0600000 -04:002013-07-24 04:00:03.073 2013-07-24 04:00:03.0730000 +00:00 2013-07-24 08:00:03.073 2013-07-24 08:00:03.0730000 +00:00 2013-07-24 00:00:03.0730000 -04:002013-07-23 04:00:03.273 2013-07-23 04:00:03.2730000 +00:00 2013-07-23 08:00:03.273 2013-07-23 08:00:03.2730000 +00:00 2013-07-23 00:00:03.2730000 -04:002013-07-20 04:00:02.870 2013-07-20 04:00:02.8700000 +00:00 2013-07-20 08:00:02.870 2013-07-20 08:00:02.8700000 +00:00 2013-07-20 00:00:02.8700000 -04:002013-07-19 04:00:03.780 2013-07-19 04:00:03.7800000 +00:00 2013-07-19 08:00:03.780 2013-07-19 08:00:03.7800000 +00:00 2013-07-19 00:00:03.7800000 -04:00---------------------------------- ---------------------------------- ----------------------------------没有 UTC 偏移量 UTC 时间 没有 UTC 偏移量 时间都错了

它们都没有返回所需的值.

谁能建议一些能返回我直觉想要的东西?

解决方案

`编辑:为 SQL Server 2016 更新了更好的答案`

选择ChangeDate，--原始日期时间值ChangeDate AT TIME ZONE '东部标准时间' AS ChangeDateOffset来自审核日志

AT TIME ZONE 考虑了夏令时是否在转换日期的时间生效.即使它在东部标准时间" 中显示标准"，它也会为您提供白天时间:

ChangeDate ChangeDateOffset------------------------ -------------------------------------------2019-01-21 09:00:00.000 2019-01-21 09:00:00.000 -05:002019-02-21 09:00:00.000 2019-02-21 09:00:00.000 -05:002019-03-21 09:00:00.000 2019-03-21 09:00:00.000 -04:00 <--节省时间2019-04-21 09:00:00.000 2019-04-21 09:00:00.000 -04:00 <-- 节省时间2019-05-21 09:00:00.000 2019-05-21 09:00:00.000 -04:00 <--节省时间2019-06-21 09:00:00.000 2019-06-21 09:00:00.000 -04:00 <--节省时间2019-07-21 09:00:00.000 2019-07-21 09:00:00.000 -04:00 <--节省时间2019-08-21 09:00:00.000 2019-08-21 09:00:00.000 -04:00 <--节省时间2019-09-21 09:00:00.000 2019-09-21 09:00:00.000 -04:00 <--节省时间2019-10-21 09:00:00.000 2019-10-21 09:00:00.000 -04:00 <--节省时间2019-11-21 09:00:00.000 2019-11-21 09:00:00.000 -05:002019-12-21 09:00:00.000 2019-12-21 09:00:00.000 -05:00

至于如何避免对字符串Eastern Standard Time进行硬编码，并使用服务器的当前时区?你是 SOL.

`SQL Server 2016 之前的原始答案`

我想通了.诀窍是有一个内置的 SQL Server 函数 ToDateTimeOffset，将任意偏移量信息附加到任何提供的datetime.

例如，相同的查询:

SELECT ToDateTimeOffset('2013-07-25 15:35:27', -240) -- -240 分钟SELECT ToDateTimeOffset('2013-07-25 15:35:27', '-04:00') -- -4 小时

都返回:

2013-07-25 15:35:27.0000000 -04:00

注意:ToDateTimeOffset 的偏移参数可以是:

一个整数，代表分钟数
一个字符串，代表小时和分钟({+|-}TZH:THM格式)

`我们需要服务器当前的 UTC 偏移量`

接下来我们需要服务器当前与 UTC 的偏移量.有两种方法可以让 SQL Server 返回我们来自 UTC 的 integer 分钟数:

DATEPART(TZOFFSET, SYSDATETIMEOFFSET())DATEDIFF(分钟，GETUTCDATE()，GETDATE())

都返回

<代码>-240

将其插入 TODATETIMEOFFSET 函数:

选择 ToDateTimeOffset('2013-07-25 15:35:27',DATEPART(TZOFFSET, SYSDATETIMEOFFSET())——例如-240)

返回我想要的 datetimeoffset 值:

2013-07-25 15:35:27.0000000 -04:00

总而言之

现在我们可以有一个更好的函数来将日期时间转换为日期时间偏移:

CREATE FUNCTION dbo.ToDateTimeOffset(@value datetime2)返回日期时间偏移为开始/*将没有任何时区偏移的日期/时间转换为 datetimeoffset 值，使用服务器与 UTC 的当前偏移量.为此，我们使用内置的 ToDateTimeOffset 函数；它使用 datetimeoffset 值附加时区偏移信息.诀窍是在本地服务器时间和 UTC 之间使用 DATEDIFF(minutes)获取偏移参数.例如:日期部分(TZOFFSET，SYSDATETIMEOFFSET())返回整数-240对于 EDT(东部夏令时间)的人，比 UTC 晚 4 小时(240 分钟).将该值传递给 SQL Server 函数:TODATETIMEOFFSET(@value, -240)*/返回 TODATETIMEOFFSET(@value, DATEPART(TZOFFSET, SYSDATETIMEOFFSET()))结尾;

示例用法

选择 TOP 5改变日期，dbo.ToDateTimeOffset(ChangeDate) AS ChangeDateOffset来自审核日志

返回所需的:

ChangeDate ChangeDateOffset==========================================================2013-07-25 04:00:03.060 2013-07-25 04:00:03.0600000 -04:002013-07-24 04:00:03.073 2013-07-24 04:00:03.0730000 -04:002013-07-23 04:00:03.273 2013-07-23 04:00:03.2730000 -04:002013-07-20 04:00:02.870 2013-07-20 04:00:02.8700000 -04:002013-07-19 04:00:03.780 2013-07-19 04:00:03.7800000 -04:00

如果内置函数能做到这一点，那将是理想的:

TODATETIMEOFFSET(值)

而不必创建一个重载":

dbo.ToDateTimeOffset(值)

<块引用>

注意:任何代码都会发布到公共领域.无需署名.

How can i convert an SQL Server datetime value to a datetimeoffset value?

For example, an existing table contains datetime values that are all in "local" server time.

SELECT TOP 5 ChangeDate FROM AuditLog

ChangeDate
=========================
2013-07-25 04:00:03.060
2013-07-24 04:00:03.073
2013-07-23 04:00:03.273
2013-07-20 04:00:02.870
2013-07-19 04:00:03.780

My server (happens) to be (right now, today) four hours behind UTC (right now, in the U.S. Eastern timezone, with Daylight Savings active):

SELECT SYSDATETIMEOFFSET()

2013-07-25 14:42:41.6450840 -04:00

i want to convert the stored datetime values into datetimeoffset values; using the server's current timezone offset information.

The values i desire are:

ChangeDate               ChangeDateOffset
=======================  ==================================
2013-07-25 04:00:03.060  2013-07-25 04:00:03.0600000 -04:00
2013-07-24 04:00:03.073  2013-07-24 04:00:03.0730000 -04:00
2013-07-23 04:00:03.273  2013-07-23 04:00:03.2730000 -04:00
2013-07-20 04:00:02.870  2013-07-20 04:00:02.8700000 -04:00
2013-07-19 04:00:03.780  2013-07-19 04:00:03.7800000 -04:00

You can see the desirable characteristics:

2013-07-19 04:00:03.7800000 -04:00
\_________________________/ \____/
           |                  |
      a "local" datetime      the offset from UTC

But instead the actual values are:

SELECT TOP 5
   ChangeDate,
   CAST(ChangeDate AS datetimeoffset) AS ChangeDateOffset
FROM AuditLog

ChangeDate               ChangeDateOffset
=======================  ==================================
2013-07-25 04:00:03.060  2013-07-25 04:00:03.0600000 +00:00
2013-07-24 04:00:03.073  2013-07-24 04:00:03.0730000 +00:00
2013-07-23 04:00:03.273  2013-07-23 04:00:03.2730000 +00:00
2013-07-20 04:00:02.870  2013-07-20 04:00:02.8700000 +00:00
2013-07-19 04:00:03.780  2013-07-19 04:00:03.7800000 +00:00

With the invalid characteristics:

2013-07-19 04:00:03.7800000 +00:00
\_________________________/ \____/
                              ^
                              |
                             No offset from UTC present

So i try other things randomly:

SELECT TOP 5
    ChangeDate, 
    CAST(ChangeDate AS datetimeoffset) AS ChangeDateOffset,
    DATEADD(minute, DATEDIFF(minute, GETDATE(), GETUTCDATE()), ChangeDate) AS ChangeDateUTC,
    CAST(DATEADD(minute, DATEDIFF(minute, GETDATE(), GETUTCDATE()), ChangeDate) AS datetimeoffset) AS ChangeDateUTCOffset,
    SWITCHOFFSET(CAST(ChangeDate AS datetimeoffset), DATEDIFF(minute, GETUTCDATE(), GETDATE())) AS ChangeDateSwitchedOffset
FROM AuditLog
ORDER BY ChangeDate DESC

With results:

ChangeDate               ChangeDateOffset                    ChangeDateUTC            ChangeDateUTCOffset                 ChangeDateSwitchedOffset
=======================  ==================================  =======================  ==================================  ==================================
2013-07-25 04:00:03.060  2013-07-25 04:00:03.0600000 +00:00  2013-07-25 08:00:03.060  2013-07-25 08:00:03.0600000 +00:00  2013-07-25 00:00:03.0600000 -04:00
2013-07-24 04:00:03.073  2013-07-24 04:00:03.0730000 +00:00  2013-07-24 08:00:03.073  2013-07-24 08:00:03.0730000 +00:00  2013-07-24 00:00:03.0730000 -04:00
2013-07-23 04:00:03.273  2013-07-23 04:00:03.2730000 +00:00  2013-07-23 08:00:03.273  2013-07-23 08:00:03.2730000 +00:00  2013-07-23 00:00:03.2730000 -04:00
2013-07-20 04:00:02.870  2013-07-20 04:00:02.8700000 +00:00  2013-07-20 08:00:02.870  2013-07-20 08:00:02.8700000 +00:00  2013-07-20 00:00:02.8700000 -04:00
2013-07-19 04:00:03.780  2013-07-19 04:00:03.7800000 +00:00  2013-07-19 08:00:03.780  2013-07-19 08:00:03.7800000 +00:00  2013-07-19 00:00:03.7800000 -04:00
                         ----------------------------------                           ----------------------------------  ----------------------------------
                                              No UTC offset                           Time in UTC          No UTC offset  Time all wrong

None of them return the desired values.

Can anyone suggest something that returns what i intuitively want?

解决方案

Edit: Updated better answer for SQL Server 2016

SELECT 
   ChangeDate,  --original datetime value
   ChangeDate AT TIME ZONE 'Eastern Standard Time' AS ChangeDateOffset
FROM AuditLog

The AT TIME ZONE takes into account whether daylight savings was in effect at the time of the date being converted. And even though it says "Standard" in "Eastern Standard Time", it will give you daylight times as well:

ChangeDate               ChangeDateOffset
-----------------------  ------------------------------
2019-01-21 09:00:00.000  2019-01-21 09:00:00.000 -05:00
2019-02-21 09:00:00.000  2019-02-21 09:00:00.000 -05:00
2019-03-21 09:00:00.000  2019-03-21 09:00:00.000 -04:00  <-- savings time
2019-04-21 09:00:00.000  2019-04-21 09:00:00.000 -04:00  <-- savings time
2019-05-21 09:00:00.000  2019-05-21 09:00:00.000 -04:00  <-- savings time
2019-06-21 09:00:00.000  2019-06-21 09:00:00.000 -04:00  <-- savings time
2019-07-21 09:00:00.000  2019-07-21 09:00:00.000 -04:00  <-- savings time
2019-08-21 09:00:00.000  2019-08-21 09:00:00.000 -04:00  <-- savings time
2019-09-21 09:00:00.000  2019-09-21 09:00:00.000 -04:00  <-- savings time
2019-10-21 09:00:00.000  2019-10-21 09:00:00.000 -04:00  <-- savings time
2019-11-21 09:00:00.000  2019-11-21 09:00:00.000 -05:00
2019-12-21 09:00:00.000  2019-12-21 09:00:00.000 -05:00

As for how do you avoid hard-coding the string Eastern Standard Time, and use the current timezone of the server? You're SOL.

Original pre-SQL Server 2016 answer

i figured it out. The trick is that there is a built-in SQL Server function ToDateTimeOffset, which attaches arbitrary offset information to any supplied datetime.

For example, the identical queries:

SELECT ToDateTimeOffset('2013-07-25 15:35:27', -240)     --  -240 minutes
SELECT ToDateTimeOffset('2013-07-25 15:35:27', '-04:00') --  -4 hours

both return:

2013-07-25 15:35:27.0000000 -04:00

Note: The offset parameter to ToDateTimeOffset can either be:

an integer, representing a number of minutes
a string, representing a hours and minutes (in {+|-}TZH:THM format)

We need the server's current UTC offset

Next we need the server's current offset from UTC. There are two ways i can have SQL Server return the the integer number of minutes we are from UTC:

DATEPART(TZOFFSET, SYSDATETIMEOFFSET()) 
DATEDIFF(minute, GETUTCDATE(), GETDATE())

both return

-240

Plugging this into the TODATETIMEOFFSET function:

SELECT ToDateTimeOffset(
      '2013-07-25 15:35:27',
      DATEPART(TZOFFSET, SYSDATETIMEOFFSET()) --e.g. -240
)

returns the datetimeoffset value i want:

2013-07-25 15:35:27.0000000 -04:00

Putting it altogether

Now we can have a better function to convert a datetime into a datetimeoffset:

CREATE FUNCTION dbo.ToDateTimeOffset(@value datetime2)
    RETURNS datetimeoffset AS
BEGIN
/*
    Converts a date/time without any timezone offset into a datetimeoffset value, 
    using the server's current offset from UTC. 
    
    For this we use the built-in ToDateTimeOffset function; 
    which attaches timezone offset information with a datetimeoffset value.
    
    The trick is to use DATEDIFF(minutes) between local server time and UTC 
    to get the offset parameter.
    
    For example:
        DATEPART(TZOFFSET, SYSDATETIMEOFFSET())
    returns the integer
        -240

    for people in EDT (Eastern Daylight Time), which is 4 hours (240 minutes) behind UTC.
    Pass that value to the SQL Server function:
        TODATETIMEOFFSET(@value, -240)
*/
    
    RETURN TODATETIMEOFFSET(@value, DATEPART(TZOFFSET, SYSDATETIMEOFFSET()))
END;

Sample usage

SELECT TOP 5
    ChangeDate, 
    dbo.ToDateTimeOffset(ChangeDate) AS ChangeDateOffset
FROM AuditLog

returns the desired:

ChangeDate               ChangeDateOffset
=======================  ==================================
2013-07-25 04:00:03.060  2013-07-25 04:00:03.0600000 -04:00
2013-07-24 04:00:03.073  2013-07-24 04:00:03.0730000 -04:00
2013-07-23 04:00:03.273  2013-07-23 04:00:03.2730000 -04:00
2013-07-20 04:00:02.870  2013-07-20 04:00:02.8700000 -04:00
2013-07-19 04:00:03.780  2013-07-19 04:00:03.7800000 -04:00

It would have been ideal if the built-in function would have just did this:

TODATETIMEOFFSET(value)

rather than having to create an "overload":

dbo.ToDateTimeOffset(value)

Note: Any code is released into the public domain. No attribution required.

这篇关于如何将 datetime 转换为 datetimeoffset?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！

如何将 datetime 转换为 datetimeoffset?