Tentative date casting in tsql

Posted by Tewr on Stack Overflow See other posts from Stack Overflow or by Tewr
Published on 2010-03-16T09:43:33Z Indexed on 2010/03/16 9:46 UTC
Read the original article Hit count: 268

Filed under:
|
|
|
|

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

© Stack Overflow or respective owner

Related posts about tsql

Related posts about sql-server-2005