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,…

Accepted Answer

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

Trouble converting VARCHAR to DATETIME in SQL (with timezone)

Solution

Problem