如何将datetime强制转换为datetimeoffset? [英] How to cast datetime to datetimeoffset?
问题描述
如何将SQL Server datetime 值转换为 datetimeoffset 值?
How can i convert an SQL Server datetime value to a datetimeoffset value?
例如，现有表包含 datetime 值，这些值都在本地" 服务器时间中.
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
我的服务器( happens )(现在，今天)比UTC(现在，在美国东部时区，启用夏令时)晚4小时:
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
我想将存储的 datetime 值转换为 datetimeoffset 值；使用服务器的当前时区偏移信息.
i want to convert the stored datetime values into datetimeoffset values; using the server's current timezone offset information.
我期望的值是:
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
但是实际值是:
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
具有无效特征:
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
有结果:
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?
推荐答案
编辑:更新了SQL Server 2016的更好答案
Edit: Updated better answer for SQL Server 2016
SELECT
ChangeDate, --original datetime value
ChangeDate AT TIME ZONE 'Eastern Standard Time' AS ChangeDateOffset
FROM AuditLog
AT TIME ZONE (AT时区)考虑了转换日期的夏令时是否有效.而且即使它在东部标准时间" 中显示了"Standard" ，它也会为您提供日光时间:
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
关于如何避免对字符串 Eastern Standard Time 进行硬编码，并使用服务器的当前时区?您就是SOL.
As for how do you avoid hard-coding the string Eastern Standard Time, and use the current timezone of the server? You're SOL.
我想通了.诀窍是有一个内置的SQL Server函数 ToDateTimeOffset ，它将任意偏移量信息附加到任何提供的 datetime .
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.
例如，相同的查询:
SELECT ToDateTimeOffset('2013-07-25 15:35:27', -240) -- -240 minutes
SELECT ToDateTimeOffset('2013-07-25 15:35:27', '-04:00') -- -4 hours
均返回:
2013-07-25 15:35:27.0000000 -04:00
注意: ToDateTimeOffset 的offset参数可以是:
Note: The offset parameter to ToDateTimeOffset can either be:
-
整数，代表分钟数 - 一个
string，代表小时和分钟(以{+ |-} TZH:THM格式)
- an
integer, representing a number of minutes - a
string, representing a hours and minutes (in{+|-}TZH:THMformat)
接下来，我们需要服务器当前与UTC的偏移量.我有两种方法可以让SQL Server返回我们距UTC的 integer 分钟数:
Next we need the server's current offset from UTC. I 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())
都返回
-240
将其插入 TODATETIMEOFFSET 函数:
SELECT ToDateTimeOffset(
'2013-07-25 15:35:27',
DATEPART(TZOFFSET, SYSDATETIMEOFFSET()) --e.g. -240
)
返回我想要的 datetimeoffset 值:
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 builtin 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;
样品用量
SELECT TOP 5
ChangeDate,
dbo.ToDateTimeOffset(ChangeDate) AS ChangeDateOffset
FROM AuditLog
返回所需的内容:
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)
无需创建过载"
dbo.ToDateTimeOffset(value)
注意:任何代码都已发布到公共领域.无需注明出处.
Note: Any code is released into the public domain. No attribution required.
这篇关于如何将datetime强制转换为datetimeoffset?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
