TRY_PARSE vs TRY_CONVERT

Question

I am currently studying for my MCSA Data Platform, I got the following question wrong and I was looking for an explanation as to why my answer was wrong as the in test explanation did not make much sense.

You have a table named AuditTrail that tracks modifications to data in other tables. The AuditTrail table is updated by many processes. Data input into AuditTrail may contain improperly formatted datetime values. You implement a process that retrieves data from various columns, but sometimes the process throws error when its unable to convert the data into valid date time values. You need to convert the data into a valid date time value using the en-US format culture code. If the conversion fails, a null value must be returned in the en-US format culture code. If the conversion fails, a null value must be returned in the column output and the conversion process must not throw error.

The options were TRY_PARSE function vs TRY_CONVERT function.

I chose TRY_PARSE but the correct answer is TRY_CONVERT.

Can anybody clarify why TRY_PARSE is not a valid choice in this example as I do not want to make this mistake in the future

Answer 1

One of the main differences between TRY_PARSE and TRY_CONVERT is that TRY_PARSE accepts a culture with a USING clause. Though poorly worded and wrapped in assumptions, this question seems to be suggesting that the input & system cultures are the same.

So in this case TRY_CONVERT would be the simpler of the two calls, since the default culture is assumed to be 'en-US'.

Had you needed to parse a french date from a string, the choice would have to be TRY_PARSE, since it allows you to specify the culture.

TRY_PARSE ('mars 18 2018' AS DATETIME USING 'fr-FR')  

Source: https://docs.microsoft.com/en-us/sql/t-sql/functions/try-parse-transact-sql?view=sql-server-2017

Answer 2

1. TRY_PARSE can only be used for converting from string to date/time or number data types where as TRY_CONVERT can be used for any general type conversions. For example, you can use TRY_CONVERT to convert a string to XML data type, where as you can do the same using TRY_PARSE

2. Converting a string to XML data type using TRY_CONVERT

   SELECT TRY_CONVERT(XML, '') AS [XML]

The above query produces the following

XML     <root></child></root>

1. Converting a string to XML data type using TRY_PARSE

   SELECT TRY_PARSE('' AS XML) AS [XML]

The above query will result in the following error
  Invalid data type xml in function TRY_PARSE

• Another difference is TRY_PARSE relies on the presence of .the .NET Framework Common Language Runtime (CLR) where as TRY_CONVERT does not.