Tentative date casting in tsql
- by Tewr
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
