TRY_PARSE, TRY_CONVERT And TRY_CAST In SQL Server

Introduction

SQL SERVER 2005 discovered a new concept TRY-CATCH to handle unexpected behavior in SQL statements. We can use TRY-CATCH inside Stored Procedure, Triggers to handle exception and raise error message. Often we need to convert one data type value to other data type. For instance, there is a string(varchar) type variable and we need to convert its date or integer type - in case string value is blank and you are trying to convert to date type then SQL SERVER will throw you exception due to type casting. In order to avoid conversion problem SQL SERVER 2012 introduced the following three functions which helps us to handle type conversion exception. These functions are:

1. TRY_PARSE
2. TRY_CONVERT
3. TRY_CAST

Let’s discuss how it can be implemented:

 

TRY_PARSE

 

It converts string data type to target data type(Date or Numeric). For example, source data is string type and we need to covert to date type. If conversion attempt fails it returns NULL value.

 

Syntax: TRY_PARSE (string_value AS data_type [ USING culture ]) 

• String_value – This is argument is source value which is NVARCHAR(4000) type.
• Data_type – This argument is target data type either date or numeric.
• Culture – It is an optional argument which helps to convert the value to in Culture format. Suppose you want to display the date in French, then you need to pass culture type as ‘Fr-FR’. If you will not pass any valid culture name, then PARSE will raise an error. 

Examples

DECLARE @ fakeDate AS varchar(10);
DECLARE @ realDate AS VARCHAR(10);
SET @fakeDate = 'iamnotadate';
SET @realDate = '13/09/2015;
SELECT TRY_PARSE(@fakeDate AS DATE); --NULL
SELECT TRY_PARSE(@realDate AS DATE); -- 2015-09-13
SELECT TRY_PARSE(@realDate AS DATE USING 'Fr-FR'); -- 2015-09-13

First query attempts to convert a non-date to date type, it fails and returns NULL value. Second query successfully converts to date type. Third query also successfully converts but in French forma t(Here it is not showing any difference you can use other culture to see the difference).

TRY_CONVERT

It converts value to specified data type and if conversion fails it returns NULL. For example, source value in string format and we need date/integer format. Then this will help us to achieve the same.

Syntax: TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

• Data_type - The datatype into which to convert. Here length is an optional parameter which helps to get result in specified length.
• Expression - The value to be convert
• Style - It is an optional parameter which determines formatting. Suppose you want date format like “May, 18 2013” then you need pass style as 111. More on style visit here.

Examples:

DECLARE @sampletext AS VARCHAR(10);
SET @sampletext = '123456';
DECLARE @ realDate AS VARCHAR(10);
SET @realDate = '13/09/2015’;
SELECT TRY_CONVERT(INT, @sampletext); -- 123456
SELECT TRY_CONVERT(DATETIME, @sampletext); -- NULL
SELECT TRY_CONVERT(DATETIME, @realDate, 111); -- Sep, 13 2015

First query converts the text to integer successfully, but in second query the conversion fails and it returns NULL. Third query successfully converts string value to date type with specified formats.

TRY_CAST

It converts value to specified data type and if conversion fails it returns NULL. For example, source value in string format and we need it in double/integer format. Then this will help us in achieving it.

 

Syntax: TRY_CAST ( expression AS data_type [ ( length ) ] ) 

• Expression - The source value which will go to cast.
• Data_type - The target data type the source value will cast.
• Length - It is an optional parameter that specifies the length of target data type.

Examples

DECLARE @sampletext AS VARCHAR(10);
SET @sampletext = '123456';

SELECT TRY_CAST(@sampletext AS INT); -- 123456
SELECT TRY_CAST(@sampletext AS DATE); -- NULL

Firstly, query converts the text to integer successfully, but in second query the conversion fails and it returns NULL.

Analysis

Above we discussed about 3 types of conversion functions in SQL SERVER. TRY_PARSE converts string to date/numeric only. TRY_CONVERT converts source value to target data type. But here you can use style as optional parameter to format the date. TRY_CAST do the same job like TRY_CONVERT except style as extra parameter. Here you can’t pass style param.

Secondly, TRY_CONVERT is not supported in SQL Azure so use TRY_CAST or TRY_PARSE instead of that.

Thirdly, TRY_PARSE is not a SQL Native function rather it is a .NET runtime dependent function. It also facilitates to parse data culture wise which not there in TRY_CONVERT and TRY_CAST. It may create performance overhead but it tries its best to parse the data to specified data type. Let’s see the following example:

SELECT TRY_PARSE('Saturday, 08 June 2013' AS DATETIME) -- June, 08 2013 00:00:00
SELECT TRY_CONVERT(DATETIME, 'Saturday, 08 June 2013'); -- NULL

Above query successfully parsed to datetime, but it failed when we use convert. So TRY_PARSE always do its best to parse the data.

And one more thing I want to draw attention is TRY_CONVERT and TRY_CAST throw errors for explicit conversions - these conversions are not possible. More on data type explicit conversion visit here. See the following examples:

SELECT TRY_CONVERT(text, 4); -- Explicit conversion from data type int to text is not allowed.
SELECT TRY_CAST(4 AS text); -- Explicit conversion from data type int to text is not allowed.

From above discussion we come to know that we need to use those functions wisely based on requirement, data, data type so that it will be helpful.

Conclusion

In this article we discussed power of TRY_PARSE, TRY_CONVERT and TRY_CAST in SQL SERVER. It helps developer to handle type conversion exception without TRY-CATCH block. These functions are like Int.TryParse("123", out int result) in .NET Framework.