SQL ISDATE returns false for string formatted as ISO w/timezone (127)

Question

I'm attempting to take a string, determine if it is a date (date time or time) and then convert it to the respective data type with the SQL server convert format constants. However, I'm having a lot of trouble with 127 "yyyy-mm-ddThh:mi:ss.mmmZ" in that ISDATE returns false for a string formatted this way.

SELECT ISDATE('2018-07-21 15:14:00.5206914 -06:00') -- returns false

Why is that? And is there any build in sql server function I'm not aware of that can test to see if this is a valid date string?

Answer 1

The isdate function accepts certain dates (depending on the date format and language), but never a datetimeoffset. The offset is the -06:00 part at the end of your example.

You could use try_convert with style 120 (yyyy-mm-dd hh:mi:ss(24h) ODBC canonical) instead:

SELECT TRY_CONVERT(datetimeoffset, '2018-07-21 15:14:00.5206914 -06:00', 120);
-->
2018-07-21 15:14:00.5206914 -06:00

The try_convert function will return null if the passed expression is not a valid datetimeoffset.