How to convert a DateTime string to a DateTime in SQL Server

4
votes

I have a DateTime varchar in this format: 2005-08-08T00:00:00+01:00.

  1. Does this format have a name? (it's not ISO8601. Is it RFC3339?)
  2. How can I convert it to a DateTime using Transact-Sql?

EDIT Here's a summary answer, cobbled together from others input:

  1. It is ISO8601 with a time offset from UTC. If it was UTC it would end with a 'Z' instead of '+01:00'. wikipedia

  2. 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'

    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__::[+-]:'

Results:

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
4
tsqlsql-server-2005datetime-format
Please check this page--> sqlusa.com/bestpractices/datetimeconversion - rt2800
Yes, I found that page using google. Couldn't find my format in there though. - Colin
One thing worth noting is that your SUBSTRING() statement removes the minutes component of the timezone, meaning it will be incorrect in timezones that aren't whole hours relative to UTC. e.g. India which is UTC+5:30. - Rory
oh, also if the format is something like 2005-08-08T00:00:00-01:00, i.e. the offset is minus instead of positive, your result will be incorrect. - Rory
Rory - I have edited the sql to calculate the utc correctly when timezones aren't whole hours, but I can't see anything wrong with the calculation when offset is minus - see the results above - Colin

4 Answers

11
votes

In SQL Server 2008 you can use the datetimeoffset data type.

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

Output:

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

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

Output:

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

Its ISO8601 with time zone Z

use 

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

see MSDN Explanation

EDIT: I think it something to do with the zone on the end I originally assumed tSql would accept that but turns out it wont.

2
votes

You can do it a really ugly way, where you get the date first, then get the time and add the offset as hour:

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

or a consolidated version:

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

if you don't need the offset, then:

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
0
votes

Per the selected answer, datetimeoffset is a good solution. However, be careful sql server side with built in functions. (I tried to paste this as a comment to the accepted answer, but it would not format the code sample)

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