CODEWITHSUNDEEP
sqlsql-serverdatetime
Timothy Vogel
Timothy Vogel

Reputation: 1597

SQL Server Convert ISO 8601 not working as documented

Per MSDN convert should properly parse ISO 8601 dates with timezone using 127 as the style parameter.

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.

All of the following are valid ISO 8601 dates but return Conversion failed when converting date and/or time from character string.

select convert(datetime, N'2014-02-07T13:51:00+07:00', 127)
select convert(datetime, N'2014-02-07T13:51:00+07', 127)
select convert(datetime, N'2006-12-12T23:45:12-08:00', 127)

Anyone have a solution or workaround for this issue?

Upvotes: 7

Views: 3993

Answers (2)

Vinay Pandey
Vinay Pandey

Reputation: 8913

use datetimeoffset or datetime2 instead of datetime

Upvotes: 2

Mitch Wheat
Mitch Wheat

Reputation: 300489

Workaround?: Use datetimeoffset:

select convert(datetimeoffset, N'2014-02-07T13:51:00+07:00', 127) --<-- This one works...
select convert(datetimeoffset, N'2014-02-07T13:51:00+07', 127)
select convert(datetimeoffset, N'2006-12-12T23:45:12-08:00') --<-- and this one works...

Upvotes: 5

Related Questions