Friday, November 20, 2015

ISO8601 Date conversion with offset for T-SQL

Quick Description:  Attempting to convert a string value ISO 8601 date with a date time offset in T-SQLwill result in the error Conversion failed when converting date and/or time from character string.

When attempting to convert a string such as

   select convert(datetime, '2015-09-30T08:01:30.765-07:00')

It returns the error:

    Msg 241, Level 16, State 1, Line 1

    Conversion failed when converting date and/or time from character string.
This seems odd since datetime supports ISO8601as style 126 and 127.  It doesn't, however, support the explicit full offset (in this case the -07:00, it supports the Z for UTC). 

This can be solved a number of ways, depending on your SQL Server version and what you're trying to do.

ISO8601 values with offset can't be directly converted to datetime, but they can be converted or cast to datetimeoffset (which can, in turn, be converted to datetime).

To convert a value inline for use as a datetime
   SELECT convert(datetime, CONVERT(datetimeoffset, '2015-09-30T08:01:30.765-07:00'))

To query the value directly
   select case
    when cast(getdate() as datetimeoffset) > cast('2015-09-30T08:01:30.765-07:00' as datetimeoffset) then 1 else 0
    end as Is_Today_Bigger