如何在SQL Server中将DateTime字符串转换为DateTime [英] How to convert a DateTime string to a DateTime in SQL Server
问题描述
我有一个DateTime varchar,格式为:2005-08-08T00:00:00 + 01:00。
I have a DateTime varchar in this format: 2005-08-08T00:00:00+01:00.
- 这样做格式有名字吗? (不是ISO8601。是RFC3339吗?)
- 如何使用Transact-Sql将其转换为DateTime?
编辑
这是一个汇总的答案,由其他输入拼凑而成:
EDIT Here's a summary answer, cobbled together from others input:
- 它是 ISO8601,与UTC的时间有偏差。如果是UTC,则以 Z而不是 +01:00结尾。 维基百科
-
您可以将其转换到本地时间或utc如下:
- It is ISO8601 with a time offset from UTC. If it was UTC it would end with a 'Z' instead of '+01:00'. wikipedia
You can convert it to local time or to utc as follows:
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'
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'
选择@d作为输入,以CONVERT(DATETIME,LEFT(@d,19),126)作为LocalDate
,DATEADD(MINUTE,-CAST((SUBSTRING (@d,20,1)+ RIGHT(@d,2))AS
INT),DATEADD(小时
,-CAST(SUBSTRING(@d,20,3)AS INT)
,CONVERT(DATETIME,LEFT(@d,19),126)))作为UtcDate WHERE @d LIKE' _ -- _T __:: [+-] :'
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
推荐答案
在SQL Server中 2008 ,则可以使用 datetimeoffset
数据类型。
In SQL Server 2008 you can use the datetimeoffset
data type.
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日期和偏移量:
In SQL Server 2005 and earlier you can compute the UTC date and offset:
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
这篇关于如何在SQL Server中将DateTime字符串转换为DateTime的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!