SQL Server Portal

In my previous post, I discussed about PARSE function. In this function, if the data type is not compatible and could not be parsed then this function will generate an error. Now, we have the solution for it namely “TRY_PARSE”. SQL SERVER has introduced this (TRY_PARSE) built in conversion function in 2012. This function is very useful because most of the time due to the incorrect data , your system may generate errors. And if you use this function it will return NULL in case of error.
Lets discuss its syntax , purpose and examples in detail.

TRY_PARSE

Syntax :

TRY_PARSE ( string_value AS data_type [ USING culture ] )

Purpose :
This function tries to parse string value (nvarchar(4000) data type) to datetime or numeric data types. But if it fails to parse, it will return NULL value. We need to provide three parameters to this function string value , data type & culture parameter respectively. String value & data type parameter are mandatory but the culture parameter is optional . As the culture parameter is option so, by default it picks up the culture from the session.It uses the .NET Framework Common Language Run time(CLR). Before using this function, we should also know, that it cost extra overhead in the performance.

Example-1 : TRY_PARSE – Successful

SELECT TRY_PARSE('2012-08-22' AS datetime2 USING 'en-US')
--Result
2012-08-22 00:00:00.0000000

Example-2 : TRY_PARSE – Unsuccessful
If the parsing will be unsuccessful, it will return NULL value.

SELECT TRY_PARSE('datetime2' AS datetime2 USING 'en-US')
--Result
NULL

Example-3 : TRY_PARSE – Real World Example with culture keyword
In this example, we will try to parse a valid and invalid date.

Select IIF(TRY_PARSE('2012-08-22' AS datetime2 USING 'en-US') is NULL
,'Invalid Date format' ,'Valid Date format')
--Result
Valid Date format

Select IIF(TRY_PARSE('2012-08-222' AS datetime2 USING 'en-US') is NULL
,'Invalid Date format' ,'Valid Date format')
--Result
Invalid Date format

Example-4 : TRY_PARSE – Real World Example with Language keyword
In this example, we will try to parse a valid and invalid date with the language keyword instead of culture.

Set Language 'English'
Select IIF(TRY_PARSE('2012-08-22' AS datetime2) is NULL
,'Invalid Date format' ,'Valid Date format')
--Result
Valid Date format

Select IIF(TRY_PARSE('2012-08-222' AS datetime2) is NULL
,'Invalid Date format' ,'Valid Date format')
--Result
Invalid Date format

Reference : MSDN