Tentative date casting in tsql

Question

I am looking for something like TRYCAST in TSQL or an equivalent method / hack.

In my case I am extracting some date data from an xml column.

The following query throws "Arithmetic overflow error converting expression to data type datetime." if the piece of data found in the xml cannot be converted to datetime (in this specific case, the date is "0001-01-01" in some cases). Is there a way to detect this exception before it occurs?

select
      [CustomerInfo].value('(//*:InceptionDate/text())[1]', 'datetime')
  FROM Customers

An example of what I am trying to achieve in pseudocode with an imagined tsql function TRYCAST(expr, totype, defaultvalue):

select
      TRYCAST(
       [CustomerInfo].value('(//*:InceptionDate/text())[1]', 'nvarchar(100)'), 
       datetime, 
       null)
  FROM Customers

Answer 1

You could try excluding the node when it is less than the minimum datetime SQL Server allows:

SELECT
  [CustomerInfo].value('(//*:InceptionDate/text())[1][.>=''1753-01-01'']', 'datetime')
FROM 
  Customers

A less assumptive way to do this would be:

SELECT
  CASE 
    WHEN ISDATE([CustomerInfo].value('(//*:InceptionDate/text())[1]'))
    THEN CONVERT(DATETIME, [CustomerInfo].value('(//*:InceptionDate/text())[1]'))
    ELSE NULL
  END
FROM 
  Customers