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: 301
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