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 ] )  
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

Here is the result set:

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

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

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

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

The output will look like this:

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

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

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

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

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.