How to convert varchar with timezone to datetime in sql

Question

I serialize my c# object to xml, then got varchar such as '2009-05-09T13:50:59.6361485+08:00'.

But SQL Server return following messgae: 'Result: Msg 241: Conversion failed when converting date and/or time -- from character string.'

after I execute following sql: declare @msg xml

set @msg='<root><date>2009-05-09T13:50:59.6361485+08:00</date></root>'

select @msg.value(N'(//root/date/text())[1]','datetime')

Answer 1

Try this:

declare @msg xml
set @msg='<root><date>2009-05-09T13:50:59.6361485+08:00</date></root>'
select @msg.value(N'(xs:dateTime((//root/date/text())[1]))', 'datetime')

The problem is, the datetime format in your XML has more precision in the 'seconds' value than the value() function (and its underlying call to CAST or CONVERT) can handle. See http://msdn.microsoft.com/en-us/library/ms187928(SQL.90).aspx. Conversion type 126 and 127 require .mmm (3 digits of precision), whereas in your original value you have 7.

For debugging steps, consider this:

select @msg.value(N'(//root/date/text())[1]', 'varchar(100)')
> 2009-05-09T13:50:59.6361485+08:00

select @msg.value(N'(xs:dateTime((//root/date/text())[1]))', 'varchar(100)')
> 2009-05-09T05:50:59.636Z