Trouble converting VARCHAR to DATETIME in SQL (with timezone)

Question

I'm trying to use the SQL CONVERT command to transform a VARCHAR to a DATETIME in SQL Server 2012. I'm following the instructions and I want to use the date/time formats described on this MSDN page: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Based on this, format #127 is described as follows:

• ISO8601 with time zone Z.
• yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)
• When the value for milliseconds (mmm) is 0, the millisecond value is not displayed. For example, the value '2012-11-07T18:26:20.000 is displayed as '2012-11-07T18:26:20'.
• The optional time zone indicator, Z, is used to make it easier to map XML datetime values that have time zone information to SQL Server datetime values that have no time zone. Z is the indicator for time zone UTC-0. Other time zones are indicated with HH:MM offset in the + or - direction. For example: 2006-12-12T23:45:12-08:00.

However, when I try to actually do the conversion (following this format), it fails...

SELECT CONVERT(datetime, '2014-07-14T10:00:00.000-08:00', 127)

... with the following error:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Anyone know why this isn't working?

EDIT: The example isn't working either:

SELECT CONVERT(datetime, '2006-12-12T23:45:12-08:00', 127)

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Answer 1

Since it's a string with a timezone in it, you need to convert it to a DATETIMEOFFSET type:

SELECT CONVERT(DATETIMEOFFSET, '2014-07-14T10:00:00.000-08:00', 127)

(No column name)
----------------------------------
2014-07-14 10:00:00.0000000 -08:00

This works just fine. Once you have that, you can then convert it to a local DATETIME as needed, e.g. with

DECLARE @DateTimeOffset DATETIMEOFFSET 
CAST(@DateTimeOffset AS DATETIME)

or by switching the value to another timezone with

SELECT SWITCHOFFSET(@DateTimeOffset, '+01:00')