Tentative date casting in tsql
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,
开发者_JAVA技巧 null)
FROM Customers
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
精彩评论