如何在SQL Server中将DateTime字符串转换为DateTime

Question

我有一个DateTime varchar，格式如下：2005-08-08T00:00:00+01:00。

1. 这种格式有名称吗？（不是ISO8601。是RFC3339吗？）
2. 如何使用Transact-Sql将其转换为DateTime？

编辑 以下是从其他人的意见中汇总的答案：

1. 它是带有UTC时间偏移量的ISO8601格式。如果它是UTC时间，它会以“Z”结尾，而不是“+01:00”。维基百科

2. 您可以按以下方式将其转换为本地时间或UTC时间：

   DECLARE @d VARCHAR(25) SET @d = '2007-08-08T00:01:00+01:00' SET @d = '2007-08-08T00:01:00-01:00' SET @d = '2007-08-08T00:01:00+05:30'

   SELECT @d as Input, CONVERT(DATETIME, LEFT(@d, 19), 126) AS LocalDate , DATEADD(MINUTE , -CAST((SUBSTRING(@d, 20, 1) + RIGHT(@d, 2)) AS INT) , DATEADD(HOUR ,-CAST(SUBSTRING(@d, 20, 3) AS INT) , CONVERT(DATETIME, LEFT(@d, 19), 126))) as UtcDate WHERE @d LIKE '_--_T__::[+-]:'

结果：

Input                     LocalDate               UtcDate
------------------------- ----------------------- -----------------------
2007-08-08T00:01:00+01:00 2007-08-08 00:01:00.000 2007-08-07 23:01:00.000

2007-08-08T00:01:00-01:00 2007-08-08 00:01:00.000 2007-08-08 01:01:00.000

2007-08-08T00:01:00+05:30 2007-08-08 00:01:00.000 2007-08-07 18:31:00.000

- Colin

---

是的，我用谷歌找到了那个页面。但是在那里没有找到我的格式。 - Colin

值得注意的一点是，您的SUBSTRING（）语句会删除时区的分钟组件，这意味着在相对于UTC不是整小时的时区中，它将不正确。例如印度，它是UTC + 5:30。 - Rory

哦，还有如果格式是像2005-08-08T00:00:00-01:00这样的，即偏移量为负数而不是正数，你的结果将是不正确的。 - Rory

Rory - 我已经编辑了 SQL，以正确计算 UTC，即当时区不是整数小时时，但我看不出当偏移量为负数时计算有什么问题 - 请参见上面的结果。 - Colin

Answer 1

在 SQL Server 2008 中，您可以使用 datetimeoffset 数据类型。

SELECT [Result] = CONVERT(datetimeoffset, '2005-08-08T00:01:00+01:00', 127)

输出：

Result
----------------------------------
2005-08-08 00:01:00.0000000 +01:00

在 SQL Server 2005 及更早版本中，您可以计算出 UTC 日期和偏移量：

SELECT [LocalDate], [OffsetMinutes], [UtcDate]
FROM
(
    SELECT [IsoDate] = '2007-08-08T00:01:00+01:00'
) A
OUTER APPLY
(
    SELECT [LocalDate] = CONVERT(datetime, LEFT([IsoDate], 19), 126)
    , [OffsetMinutes] =
        CASE SUBSTRING([IsoDate], 20, 1)
            WHEN '+' THEN +1
            WHEN '-' THEN -1
        END
        * DATEDIFF(minute, 0,
            CAST(SUBSTRING([IsoDate], 21, 5) + ':00' AS datetime))
    WHERE [IsoDate] LIKE '____-__-__T__:__:__[+-]__:__'
) B
OUTER APPLY
(
    SELECT [UtcDate] = DATEADD(minute, -[OffsetMinutes], [LocalDate])
) C

输出：

LocalDate               OffsetMinutes UtcDate
----------------------- ------------- -----------------------
2007-08-08 00:01:00.000 60            2007-08-07 23:01:00.000

- Anthony Faull

---

谢谢，但数据库是Sql Server 2005，我需要将其转换为DateTime。 - Colin

当然，你是正确的。我不能直接将字符串中的所有信息转换为DateTime。时区必须存储在其他地方（如果需要的话）。 - Colin

好的。我已经为SQL Server 2005添加了一个解决方案。 - Anthony Faull

哇！这里有很多事情要做，但我刚刚意识到的主要问题是@bluefeet和我一直在添加时区，而我们应该减去它！ - Colin

Answer 2

这是带时区Z的ISO8601时间格式

使用

SELECT CONVERT(datetime,'2005-08-08T00:00:00',126)

请参考MSDN解释

编辑： 我认为这与结尾的zone有关，最初我认为tSql会接受它，但事实证明它不会。

- AbstractChaos

---

刚在 SQL Server 2005 和 2008 中测试了一下，结果失败了。 - Taryn

哇，这很奇怪，考虑到 SELECT CONVERT(date,'2005-08-08T00:00:00+01:00',127) 是可以工作的。 - AbstractChaos

我认为日期类型会起作用，因为它在datetime无法处理时会忽略时区，并且我应该已经指定了Sql Server 2005。但在2005中，“日期类型未定义系统类型”。 - Colin

那个链接上提到了一些有关127类型的信息，但没有指出它不能转换为日期时间... - AbstractChaos

Answer 3

您可以通过一种非常丑陋的方式来实现，首先获取日期，然后获取时间并将偏移量添加为小时：

declare @d varchar(50)
set @d = '2005-08-08T00:00:00+01:00'

select Convert(datetime, left(@d, 10)) 
    + DateAdd(hour, Cast(substring(@d, 21, 2) as int), convert(datetime, substring(@d, 12, 8)))

或者一份汇总版本：

SELECT DateAdd(hour, Cast(substring(@d, 21, 2) as int), CONVERT(datetime, LEFT(@d, 19) ,127))

Final Result:
2005-08-08 01:00:00.000

如果您不需要偏移量，则：

declare @d varchar(50)
set @d = '2005-08-08T00:00:00+01:00'

select Convert(datetime, left(@d, 10)) 
    +  convert(datetime, substring(@d, 12, 8))

Result:
2005-08-08 00:00:00.000

- Taryn

---

考虑到这一点，以及Anthony Faull的答案。字符串包含日期时间和时区，但是Sql Server 2005只能存储DateTime。所以我必须删除时区，或将其转换为我决定存储DateTime的时区（GMT）。 - Colin

这是更简洁的方法：SELECT DateAdd(hour, Cast(substring(@d, 21, 2) as int), CONVERT(datetime, LEFT(@d, 19) ,127))。我建议你编辑你的答案... - Colin

我使用的是合并后的代码。它将时间转换为GMT格式以便存储在数据库中（我会失去它原本所在的时区，但这没关系）。谢谢。 - Colin

请注意，这里应该从合并版本中减去偏移量，而不是加上它。 - Colin

Answer 4

如果您需要 datetime2 输出

declare @dof datetimeoffset;
set @dof = cast('2005-08-08T00:00:00+01:00' as datetimeoffset);
select dateadd(minute, datepart(tzoffset, @dof), cast(@dof as datetime2));

-- returns 2005-08-08 01:00:00.0000000

- Meowster

Answer 5

根据所选答案，datetimeoffset是一个不错的解决方案。但是，在使用内置函数时要小心SQL服务器端。(我试图将此作为评论粘贴到已接受的答案中，但它无法格式化代码示例)

select GETDATE(), CONVERT(datetimeoffset, GETDATE(), 127), CONVERT(varchar, convert(datetimeoffset, getdate(), 127)) 
-- Ugg! a SQL Server Bug!!!
-- 2013-08-16 12:54:54.090  2013-08-16 12:54:54.0900000 +00:00  2013-08-16 12:54:54.0900000 +0

select GETUTCDATE(),convert( datetimeoffset, GETUTCDATE(), 127), convert(varchar, convert( datetimeoffset, GETUTCDATE(), 127)) 
-- 2013-08-16 16:54:54.090  2013-08-16 16:54:54.0900000 +00:00  2013-08-16 16:54:54.0900000 +0

- Jim

---

如果你展示的问题是在将字符串转换为varchar时被截断了，那么这是因为你没有指定varchar的长度，而SQL Server默认为30个字符。http://msdn.microsoft.com/en-us/library/ms176089.aspx - Colin

实际上，我试图展示的是，使用datetimeoffset转换GETDATE()时应用00:00而不是正确的时区格式。您可以在GETDATE()和GETUTCDATE()之间使用datediff并手动构建字符串，但人们会认为，在使用模式127进行转换时，SQL Server会正确处理偏移量。 - Jim

Sql Server无法从datetime转换为datetimeoffset时推断偏移量，因为该信息未存储在datetime结构中。您需要使用SYSDATETIMEOFFSET()函数。请参见https://dev59.com/2F7Va4cB1Zd3GeqPLqRt#8634229。 - Colin

每天学点新东西 :) 我之前不知道这个函数，但是以后肯定会用到它。 - Jim