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

Question

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 Transact-Sql?

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

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'

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

Yes, I found that page using google. Couldn't find my format in there though. — Colin
– Colin, Commented Apr 24, 2012 at 15:32
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
– Rory, Commented Aug 22, 2012 at 9:29
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, Commented Aug 22, 2012 at 9:31
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
– Colin, Commented Aug 23, 2012 at 11:13

Anthony Faull · Accepted Answer · 2012-04-24 21:24:48Z

14

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

edited Apr 24, 2012 at 21:24

answered Apr 24, 2012 at 15:17

Anthony Faull

18.1k5 gold badges58 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Colin Over a year ago

Thanks, but the database is Sql Server 2005, and I need to convert it to a DateTime

Colin Over a year ago

Of course, you are correct. I cannot convert all the information in the string directly to a DateTime. The timezone has to be stored elsewhere (if it's needed)

Anthony Faull Over a year ago

Ok. I've added a solution for SQL Server 2005

Colin Over a year ago

Wow! There's a lot going on here, but the main thing I just realised is that @bluefeet and I have been adding the timezone when we should be subtracting it!

AbstractChaos · Accepted Answer · 2012-04-24 15:14:43Z

3

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.

edited Apr 24, 2012 at 15:14

answered Apr 24, 2012 at 15:07

AbstractChaos

4,2111 gold badge21 silver badges28 bronze badges

4 Comments

Taryn Over a year ago

just tested this in sql server 2005 and 2008 and it fails

AbstractChaos Over a year ago

Wow thats strange considering `SELECT CONVERT(date,'2005-08-08T00:00:00+01:00',127) does work

Colin Over a year ago

I expect date would work because it ignores the timezone when datetime doesn't, and I should have specified Sql Server 2005. "Type date is not a defined system type." in 2005

AbstractChaos Over a year ago

Well that link above mentions something about the 127 type but does not indicate it wouldn't convert to datetime...

Taryn · Accepted Answer · 2012-04-24 19:13:21Z

2

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

edited Apr 24, 2012 at 19:13

answered Apr 24, 2012 at 15:27

Taryn

249k57 gold badges374 silver badges409 bronze badges

4 Comments

Colin Over a year ago

Thinking about this, and the answer from Anthony Faull. The string contains the datetime and the timezone but Sql Server 2005 can only store the DateTime. So I have to drop the timezone, or convert it to the timezone I've decided to store DateTimes in (GMT)

Colin Over a year ago

And this is a more succinct way of doing it: SELECT DateAdd(hour, Cast(substring(@d, 21, 2) as int), CONVERT(datetime, LEFT(@d, 19) ,127)). I suggest you edit your answer...

Colin Over a year ago

The consolidated one is the one I used. It converts the time to GMT for storing in the database (I lose the time zone it was set in, but that's ok). Thanks

Colin Over a year ago

Be careful here. The consolidated version should subtract the offset, not add it

Jim · Accepted Answer · 2013-08-16 17:07:47Z

0

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

answered Aug 16, 2013 at 17:07

Jim

9721 gold badge8 silver badges18 bronze badges

4 Comments

Colin Over a year ago

If the problem you are demonstrating is that the string has been truncated when you convert to a varchar, then that is because you have not specified the length of the varchar and sql server defaults to 30 characters. msdn.microsoft.com/en-us/library/ms176089.aspx

Jim Over a year ago

Actually, what I was trying to demonstrate is that GETDATE() converted with datetimeoffset applies 00:00 instead of the correct timezone format. You can use datediff between GETDATE() and GETUTCDATE() and build the string manually, but one would think that SQL Server would handle the offset correctly in conversion when using convert with mode 127.

Colin Over a year ago

Sql server cannot infer the offset when converting from a datetime to a datetimeoffset because that information is not stored in the datetime structure. You need the SYSDATETIMEOFFSET() function. See stackoverflow.com/a/8634229/150342

Jim Over a year ago

Learn something new every day :) I wasn't aware of that function, but I will surely use it in the future.

Meowster · Accepted Answer · 2022-07-25 13:33:18Z

0

If you need datetime2 output

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

answered Jul 25, 2022 at 13:33

Meowster

6675 silver badges14 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

4 Comments

4 Comments

4 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

4 Comments

4 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related