SQL Server->> built-in scalar functions try_parse, try_cast, and try_convert the respective characteristics and differences

SQL Server to the current version 2014 has three functions that are used to convert the data format. Although both the cast and convert functions are already in the previous version to do this thing. The problem is that once the conversion to the target data type fails, it will cause an error.

Common features of Try_parse, Try_cast and Try_convert:

1) Even if the conversion failure will not cause the entire statement error, only in the case can not be converted to output null value;

Try_parse:

Try_parse is data that is used to convert data of a string type into a time or numeric type. It is a scalar function based on the. NET CLR Runtime. The syntax is Try_parse (<string/string column> as <data_type> [USING <culture>])

Here's an experiment with string-to-time:

SQL Server Version:

Microsoft SQL Server 2014
Enterprise Edition (64-bit) on Windows NT 6.3 <X64>

SELECTTry_parse ('20150901'  as DATETIME), Try_cast ('20150901'  as DATETIME), Try_convert (DATETIME,'20150901') SELECTTry_parse ('2015/09/01'  as DATETIME), Try_cast ('2015/09/01'  as DATETIME), Try_convert (DATETIME,'2015/09/01')SELECTTry_parse ('2015/09/01 14:14:45'  as DATETIME), Try_cast ('2015/09/01 14:14:45'  as DATETIME), Try_convert (DATETIME,'2015/09/01 14:14:45')SELECTTry_parse ('2015/09/01 14:14:45'  as DATETIME), Try_cast ('2015/09/01 14:14:45'  as DATETIME), Try_convert (DATETIME,'2015/09/01 14:14:45')SELECTTry_parse ('2015/09/01 14:14:45+0001'  as DATETIME), Try_cast ('2015/09/01 14:14:45+0001'  as DATETIME), Try_convert (DATETIME,'2015/09/01 14:14:45+0001')

The result of the above code output is as shown in

I was surprised to see Try_parse fail in the case of converting pure numbers to datetime, and I tried the same result as the date type.

If a time separator such as "-" or "/" is added, the three functions can be converted successfully.

And one thing that surprises me is that try_cast and Try_convert do not support conversions with time zones, while Try_parse can.

And when I change the colon of the fourth line of code into a colon below the Chinese, SQL Server does not recognize it.

Try_cast and Try_convert:

This pair is more of the cast and convert variants of the function, syntactically, just as an error or null value when the conversion cannot be successful.

The difference between the three is summarized as follows:

1) Try_parse only supports character-to-value or time-type, while Try_cast and try_convert support more types;

2) There is a bit more good is the character of the white space processing, as long as the space in the split symbol before and after the "2015/09/10" This can be successfully processed, but if the space is separated by itself is a whole data value part, then all can not be recognized, like "2015/0 9/10".

2) Try_parse because it is a function written by the CLR, the data format support for the source data is broader or more lenient, while the try_cast and Try_convert requirements are stricter. From the example above, Try_parse supports a time format with timezone and the other two does not support it. The scope of support for Try_parse is much more than that.

The following example proves that Try_parse is the only effort and possibility to convert the data, and the two require a very strict data format.

SELECT Try_parse ('Thursday, ' asDATETIME')  SELECT Try_convert (DATETIME'Thursday,'

SQL Server->> built-in scalar functions try_parse, try_cast, and try_convert the respective characteristics and differences