When Microsoft introduced the PARSE function in SQL Server 2012 they also introduced TRY_PARSE. PARSE is used for converting string values to date\time or numeric data types. Sometimes we don’t know ahead of time whether the value we are attempting to convert will be valid in the target data type. If it is not, then PARSE will raise an error.

That’s where TRY_PARSE comes in, PARSE will attempt to convert values exactly like the PARSE function, but where PARSE would have failed and returned an error, TRY_PARSE will simply return a NULL value.

SELECT PARSE('15-08-2018' AS date USING 'en-US')

Gives us the error:

Msg 9819, Level 16, State 1, Line 26

Error converting string value '15-08-2018' into data type date using culture 'en-US'.

This is a date expressed according to European rules, but I’ve attempted to convert it as an American date and it fails because there isn’t a fifteenth month.

I could try and validate my string value before the conversion:

SELECT ISDATE('15-08-2018');

But that returns 1 – telling me that it is a valid date after all – because my language is set to British.

My other option is simply to trap the error, and that works when I’m dealing with a single row, but if I’m processing a batch then one bad value causes the whole batch to fail.

And that’s why TRY_PARSE is useful. If I run the equivalent query:

SELECT TRY_PARSE('15-08-2018' AS date USING 'en-US');

Then that returns a Null value rather than an error.

The same holds true for numeric conversions. As discussed in the above linked article on PARSE, different cultures also differ on the separators used in grouping numbers. So, one million is 1,000,000 to the British and Americans, but 1.000.000 to a Dutch person.

As such the following statements fail:

SELECT PARSE('1,000,000' AS int USING 'nl-NL');
SELECT PARSE('1.000.000' AS int USING 'en-US');

But the following succeed:

SELECT PARSE('1,000,000' AS int USING 'en-US');
SELECT PARSE('1.000.000' AS int USING 'nl-NL');

If I want to avoid errors and just convert valid values, then TRY_PARSE comes to the rescue again. Now I can even run all the conversions in the same statement and don’t have to worry about one error causing the rest to fail:

SELECT
  TRY_PARSE('1,000,000' AS int USING 'nl-NL'),
  TRY_PARSE('1.000.000' AS int USING 'en-US'),
  TRY_PARSE('1,000,000' AS int USING 'en-US'),
  TRY_PARSE('1.000.000' AS int USING 'nl-NL');

TRY_PARSE works the same even if your errors aren’t related to cultural translations. One thing you might be using PARSE for, is being a little bit more forgiving of date formats. For instance:

  SELECT PARSE ('Friday, 25 May 2018' AS date);

That would have failed if we were using CAST or CONVERT.

But PARSE can’t cope with everything, so something like the following will give us an error:

  SELECT PARSE ('Friday, 25th May 2018' AS date);

Msg 9819, Level 16, State 1, Line 45

Error converting string value 'Friday, 25th May 2018' into data type date using culture ''.

If you attempt this with TRY_PARSE:

  SELECT TRY_PARSE ('Friday, 25th May 2018' AS date);

Then of course you just get a Null value.

This sort of behaviour is particularly useful if you have a column of data that may be of dubious quality, and you want to convert the items you can, and highlight the others for further attention.

For instance, imagine the following data in a table dbo.UncleanedDates:

If we try to use PARSE to convert those:

  SELECT Id, PARSE(DateAsText AS date)
  FROM dbo.UncleanedDates;

Then we get an error:

Msg 9819, Level 16, State 1, Line 70

Error converting string value '2018-25-05' into data type date using culture ''.

However, if we use TRY_PARSE, then we can convert those that are valid:

  SELECT Id, TRY_PARSE(DateAsText AS date)
  FROM dbo.UncleanedDates;

Returns:

And we can also use TRY_PARSE to identify the rows that would fail:

  SELECT Id, DateAsText
  FROM dbo.UncleanedDates
  WHERE TRY_PARSE(DateAsText AS date) IS NULL;

Returns:

One crazy scenario where you could use this is if you get dates in different languages - but don’t know which one a particular date is from. For instance, English, French or Spanish. Here’s a table, dbo.DatesinVariousLanguages:

You could deal with this through a query similar to the following:

  SELECT
     COALESCE(TRY_PARSE(DateAsText AS date USING 'en-US'),
     TRY_PARSE(DateAsText AS date USING 'fr-FR'),
     TRY_PARSE(DateAsText AS date USING 'es-ES'))
  FROM  dbo.DatesInVariousLanguages

Which returns to us:

I hope you never have to face such a scenario, but TRY_PARSE is there for you if you do!

Last Thoughts

A final point to note about TRY_PARSE is that like its companion the PARSE function, TRY_PARSE is implemented via CLR rather than as a native SQL function, which makes it a lot slower. In a quick comparison I converted 100,000 dates expressed as text using TRY_CONVERT and TRY_PARSE. TRY_CONVERT was about 100 times faster.

As such, where you can, you should use TRY_CONVERT or TRY_CAST instead - especially for bulk operations – as they are native SQL functions.

References

• MSDN Reference for TRY_PARSE
• The PARSE Function on SQL Server Central
• TRY_CONVERT on SQL Server Central