SQL Server TRY_PARSE Function

Summary: in this tutorial, you will learn how to use the SQL Server TRY_PARSE() function to convert a string to date/time and number types.

SQL Server TRY_PARSE() function overview #

The TRY_PARSE() function is used to translate the result of an expression to the requested data type. It returns NULL if the cast fails.

Here is the syntax of the TRY_PARSE() function:

TRY_PARSE ( expression AS data_type [ USING culture ] )  
Copy

In this syntax:

• expression evaluates to a string value of NVARCHAR(4000).
• data_type represents the data type requested for the result.
• culture is an optional string that specifies the culture in which expression is formatted. It defaults to the language of the current session. Note that the culture is not limited to the ones supported by SQL; It can accept any culture supported by .NET Framework.

SQL Server TRY_PARSE() function examples #

Let’s take some examples of using the TRY_PARSE() function.

1) Using SQL Server TRY_PARSE() function to convert a string to a date example #

This example uses the TRY_PARSE() function to convert the string '14 April 2019' to a date:

SELECT 
    TRY_PARSE('14 April 2019' AS date) result;
Copy

Here is the result set:

result
----------
2019-04-14

(1 row affected)
Copy

2) Using SQL Server TRY_PARSE() function to convert a string to a number example #

The following example uses the TRY_PARSE() function to convert the string '-1250' to an integer:

SELECT 
    TRY_PARSE('-1250' AS INT) result;
Copy

The following shows the output:

result
-----------
-1250

(1 row affected)
Copy

This statement returns NULL because the TRY_PARSE() function fails to convert the string 'ABC' to a decimal.

SELECT 
    TRY_PARSE('ABC' AS DEC) result;
Copy

The output will look like this:

result
-----------
NULL

(1 row affected)
Copy

3) Using SQL Server TRY_PARSE() function with CASE expression example #

This example uses the TRY_PARSE() function with the CASE to test expression and return the corresponding message if the cast is failed or succeeded.

SELECT 
    CASE
        WHEN TRY_PARSE('Last year' AS DATE) IS NULL
        THEN 'Cast failed'
        ELSE 'Cast succeeded'
    END AS result;
Copy

The following shows the output:

result
--------------------
Cast failed

(1 row affected)
Copy

In this tutorial, you have learned how to use the SQL Server TRY_PARSE() function to convert a string to date/time and number types.

Was this tutorial helpful?

YesNo