What's wrong with this try_convert usage?

Question

I am attempting to use the new try_convert feature in mssql2012 to convert a date/time/offset string to datetimeoffset.

The string looks like: 2013-04-25T21:56:58.077-05:00

Here's the code--I know this parsing won't work, so I was expecting the result to hit the "IS NULL" and return 'Cast Failed'. That isn't what happened--instead, I am still getting the Msg 241 date conversion error. Any ideas?

case  
   when try_convert(datetimeoffset, (cast(substring(lift.PlannedLiftDateTime,1,10) + ' ' + substring(lift.PlannedLiftDateTime,12,8) + ' ' + substring(lift.PlannedLiftDateTime,20,6) as datetimeoffset))) IS NULL
    then 'Cast Failed'
   when ltrim(rtrim(lift.PlannedLiftDateTime)) = ''
    then NULL
   else
    '~' + lift.PlannedLiftDateTime + '~'
end as PlannedLiftDateTime,

Answer 1

You're getting the error because of the CAST statement inside the try_convert - try_convert won't consume all the errors you generate, it will just return NULL if your convert fails.

If you use try_convert for both attempts it will work:

when try_convert(datetimeoffset, (try_convert(datetimeoffset, substring(@DateString,1,10) + ' ' + substring(@DateString,12,8) + ' ' + substring(@DateString,20,6)))) IS NULL

Working Fiddle of your code here.