{"id":2407,"date":"2018-06-07T21:58:04","date_gmt":"2018-06-08T04:58:04","guid":{"rendered":"https:\/\/database.guide\/?p=2407"},"modified":"2023-01-21T16:02:21","modified_gmt":"2023-01-21T06:02:21","slug":"parse-vs-try_parse-sql-server-whats-the-difference","status":"publish","type":"post","link":"https:\/\/database.guide\/parse-vs-try_parse-sql-server-whats-the-difference\/","title":{"rendered":"PARSE() vs TRY_PARSE() in SQL Server: What’s the Difference?"},"content":{"rendered":"

In SQL Server<\/a>, the PARSE()<\/a><\/code> and TRY_PARSE()<\/a><\/code> 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.<\/p>\n

If PARSE()<\/code> fails when attempting to parsing to a different data type, it will return an error. If TRY_PARSE()<\/code> fails, it will return NULL<\/code>.<\/p>\n

<\/p>\n

Example 1 – First, the Similarities<\/h2>\n

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:<\/p>\n

SELECT \n    PARSE('Fri, 8 June 2018' AS date) AS PARSE,\n    PARSE('Fri, 8 June 2018' AS date) AS TRY_PARSE;<\/pre>\n
Result:<\/p>\n
+------------+-------------+\n| PARSE      | TRY_PARSE   |\n|------------+-------------|\n| 2018-06-08 | 2018-06-08  |\n+------------+-------------+\n<\/pre>\n
As expected, they both return exactly the same result.<\/p>\n
But let’s see what happens when they are unable to parse the value to the required data type.<\/p>\n
Example 2 – When PARSE() Fails<\/h2>\n
Here’s an example of what happens when\u00a0PARSE()<\/code> is unable to parse a value to another value:<\/p>\n
SELECT PARSE('Next year' AS date) AS Result;<\/pre>\n
Result:<\/p>\n
Error converting string value 'Next year' into data type date using culture ''. \n<\/pre>\n
The operation fails because I didn’t provide a valid representation of the requested data type. In other words, PARSE()<\/code> can’t convert Next year<\/code> into a date<\/strong> data type as requested.<\/p>\n
Example 3 – When TRY_PARSE() Fails<\/h2>\n
Here’s an example when we try to parse the same value with\u00a0TRY_PARSE()<\/code>:<\/p>\n
SELECT TRY_PARSE('Next year' AS date) AS Result;<\/pre>\n
Result:<\/p>\n
+----------+\n| Result   |\n|----------|\n| NULL     |\n+----------+\n<\/pre>\n
The parse still fails, but it returns NULL<\/code> instead of an error.<\/p>\n
Example 4 – Using TRY_PARSE() with a Conditional Statement<\/h2>\n
We can take TRY_PARSE()<\/code> and test its return value. If it’s a NULL value,\u00a0we can return one thing, if it’s a non-NULL value, we can return another:<\/p>\n
SELECT \n    CASE WHEN TRY_PARSE('Next year' AS date) IS NULL\n        THEN 'Conversion failed'\n        ELSE 'Conversion succeeded'\n    END\nAS Result;<\/pre>\n
Result:<\/p>\n
+-------------------+\n| Result            |\n|-------------------|\n| Conversion failed |\n+-------------------+\n<\/pre>\n
Example 5 – TRY_PARSE() With Error<\/h2>\n
Just because TRY_PARSE()<\/code> doesn’t result in an error in the above examples, it doesn’t mean that it never<\/em> results in an error. There are times where you can still get an error while using this function.<\/p>\n
For example, you’ll get an error if you provide an invalid value as the culture<\/code> argument:<\/p>\n
SELECT TRY_PARSE('Next year' AS date USING 'Mars') AS Result;\n<\/pre>\n
Result:<\/p>\n
The culture parameter 'Mars' provided in the function call is not supported. \n<\/pre>\n
Some Notes about these Functions<\/h2>\n
Here are some points that Microsoft has to say<\/a> about these functions:<\/p>\n