In SQL Server, the PARSE() and TRY_PARSE() functions are used for translating a value into another data type. They essentially do the same thing, with one exception; how they deal with errors.

If PARSE() fails when attempting to parsing to a different data type, it will return an error. If TRY_PARSE() fails, it will return NULL.

Example 1 – First, the Similarities

Here’s an example that demonstrates how both functions return the same result when they can successfully parse the value to the required data type:

SELECT 
    PARSE('Fri, 8 June 2018' AS date) AS PARSE,
    PARSE('Fri, 8 June 2018' AS date) AS TRY_PARSE;

Result:

+------------+-------------+
| PARSE      | TRY_PARSE   |
|------------+-------------|
| 2018-06-08 | 2018-06-08  |
+------------+-------------+

As expected, they both return exactly the same result.

But let’s see what happens when they are unable to parse the value to the required data type.

Example 2 – When PARSE() Fails

Here’s an example of what happens when PARSE() is unable to parse a value to another value:

SELECT PARSE('Next year' AS date) AS Result;

Result:

Error converting string value 'Next year' into data type date using culture ''. 

The operation fails because I didn’t provide a valid representation of the requested data type. In other words, PARSE() can’t convert Next year into a date data type as requested.

Example 3 – When TRY_PARSE() Fails

Here’s an example when we try to parse the same value with TRY_PARSE():

SELECT TRY_PARSE('Next year' AS date) AS Result;

Result:

+----------+
| Result   |
|----------|
| NULL     |
+----------+

The parse still fails, but it returns NULL instead of an error.

Example 4 – Using TRY_PARSE() with a Conditional Statement

We can take TRY_PARSE() and test its return value. If it’s a NULL value, we can return one thing, if it’s a non-NULL value, we can return another:

SELECT 
    CASE WHEN TRY_PARSE('Next year' AS date) IS NULL
        THEN 'Conversion failed'
        ELSE 'Conversion succeeded'
    END
AS Result;

Result:

+-------------------+
| Result            |
|-------------------|
| Conversion failed |
+-------------------+

Example 5 – TRY_PARSE() With Error

Just because TRY_PARSE() doesn’t result in an error in the above examples, it doesn’t mean that it never results in an error. There are times where you can still get an error while using this function.

For example, you’ll get an error if you provide an invalid value as the culture argument:

SELECT TRY_PARSE('Next year' AS date USING 'Mars') AS Result;

Result:

The culture parameter 'Mars' provided in the function call is not supported. 

Some Notes about these Functions

Here are some points that Microsoft has to say about these functions:

• It’s recommended that you use PARSE() and TRY_PARSE() only for converting from string to date/time and number types. For other data types, use CAST() or CONVERT().
• These functions rely on the presence of .the .NET Framework Common Language Runtime (CLR).
• There’s a certain performance overhead in parsing the string value.
• These functions will not be remoted since they depend on the presence of the CLR. Trying to remote a function that requires the CLR would cause an error on the remote server.