What's wrong with this try_convert usage?
case-when, sql, sql-server, sql-server-2012, t-sql
Solution
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.
Problem
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, ```