How To Convert A Datetime String To A Datetime In Sql Server

I have a DateTime varchar in this format: 2005-08-08T00:00:00+01:00. Does this format have a name? (it's not ISO8601. Is it RFC3339?) How can I convert it to a DateTime using Tran

Solution 1:

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] =CASESUBSTRING([IsoDate], 20, 1)
            WHEN'+'THEN+1WHEN'-'THEN-1END* 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:

LocalDateOffsetMinutesUtcDate-----------------------------------------------------------2007-08-08 00:01:00.000 602007-08-07 23:01:00.000

Solution 2:

Its ISO8601 with time zone Z

use

SELECTCONVERT(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.

Solution 3:

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@dvarchar(50)
set@d='2005-08-08T00:00:00+01:00'selectConvert(datetime, left(@d, 10)) 
    + DateAdd(hour, Cast(substring(@d, 21, 2) asint), convert(datetime, substring(@d, 12, 8)))

or a consolidated version:

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

FinalResult:
2005-08-0801:00:00.000

if you don't need the offset, then:

declare@dvarchar(50)
set@d='2005-08-08T00:00:00+01:00'selectConvert(datetime, left(@d, 10)) 
    +convert(datetime, substring(@d, 12, 8))

Result:
2005-08-0800:00:00.000

Solution 4:

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 +0select 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