开发者

Tentative date casting in tsql

开发者 https://www.devze.com 2022-12-23 05:27 出处:网络
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.

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
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号