SQL CONVERT, CAST, TRY_CAST, TRY_CONVERT and TRY

Problem

Converting data from one type to another is a typical task a SQL developer performs with T-SQL scripts and stored procedures. If you have ever loaded data into Microsoft SQL Server, you know the importance of data types (varchar, nvarchar, nchar, numeric, decimal, int, date, datetime, etc.). At one point, an application I loaded data into via text files included dates from the 1400s. As you will see later, that will cause SQL to throw an error message. You are unable to CONVERT 1/1/1400 to a date data type explicitly. The easy fix would be to instruct the person generating the text file to stop including invalid dates. Sometimes real life is not so easy.

Solution

In this SQL tutorial, I will demonstrate three built-in functions for converting data types. What is so special about these functions, you might ask. SQL Server returns a NULL instead of throwing an error message when the conversion is not allowed. These functions have been around since SQL Server 2012. Unless you are using a non-supported version, you can take advantage of them. One of the downsides is the inferred overhead they may incur. I will be putting that claim to the test in this article.

Using SQL CAST or CONVERT Function

Let's look at a few simple examples of explicitly converting a few values into different data types using the CAST and CONVERT functions. First, try running the syntax below and see what Microsoft SQL Server returns with the CAST function.

SELECT CAST('31 Dec 12' AS DATE) AS [First Date],
       CAST('Dec 12 1776 12:38AM' AS DATE) AS [Second Date],
       CAST('Dec 12 1400 12:38AM' AS DATETIME) AS [Third Date],
       CAST('A1010B' AS INT) AS [Number 3];
GO

That's right; you are unable to convert the value Dec 12, 1400, into a date. Also, you cannot convert the value A1010B to an integer. I'm sure you can see how problematic this could become if you still want to return the other values. As mentioned above, in the perfect world, we would fix the data. Suppose you created a report where you execute CONVERT or CAST, and you cannot modify the data. In this situation, you may want to return a NULL instead.

Do you think that using CONVERT will return anything different?

SELECT CONVERT(DATE, '31 Dec 12', 101) AS [First Date],
       CONVERT(DATE, 'Dec 12 1776 12:38AM') AS [Second Date],
       CONVERT(DATETIME, 'Dec 12 1400 12:38AM', 100) AS [Third Date],
       CONVERT(INT, 'A1010B') AS [Number 3];
GO

Without adding a screenshot, what message did you get back? Unless you modified the code, my guess is another conversion error.

TRY_CAST

Let's run a similar query but instead, we will use TRY_CAST in the following example.

SELECT TRY_CAST('31 Dec 12' AS DATE) AS [First Date],
       TRY_CAST('Dec 12 1776 12:38AM' AS DATE) AS [Second Date],
       TRY_CAST('Dec 12 1400 12:38AM' AS DATETIME) AS [Third Date],
       TRY_CAST('Number 3' AS INT) AS [Number 3];
GO

You can see from the screenshot above that when you cannot perform the conversion, SQL returns a NULL. What's nice about this is it does not stop the other values from being converted.

TRY_CONVERT

Up next, we have TRY_CONVERT. Where again, SQL returns a NULL instead of an error message. The primary difference between TRY_CONVERT and TRY_CAST is that the former allows for a style parameter like the standard CONVERT. The best example of the style is if you wanted your date formatted in a specific way. For example, if you wanted a date formatted as mm/dd/yyyy, use 101.

If we run the code below, SQL does not throw an error message.

SELECT TRY_CONVERT(DATE, '31 Dec 12', 101) AS [First Date],
       TRY_CONVERT(DATE, 'Dec 12 1776 12:38AM') AS [Second Date],
       TRY_CONVERT(DATETIME, 'Dec 12 1400 12:38AM', 100) AS [Third Date],
       TRY_CONVERT(INT, 'A1010B') AS [Number 3];
GO

TRY_PARSE

Our last function is TRY_PARSE. This one is like the other two but allows you to pass in cultural information. For example, if you wanted the German date format, you could pass in 'de-DE'. Microsoft suggests only using TRY_PARSE when converting from string to date/time and number types. I rarely use TRY_PARSE and opt for one of the other two.

SELECT TRY_PARSE('Friday, 5 August 2022' AS DATETIME USING 'es-ES') AS [First Date],
       TRY_PARSE('€243' AS MONEY USING 'de-DE') AS [Euro],
       TRY_PARSE('Monday, 31 February 2022' AS DATE USING 'es-ES') AS [Second Date];
GO

Here are the results of the above query. Notice how the second date is returning a NULL. There cannot be 31 days in February.

Using CASE to Substitute

One of the wonderful things about using TRY_CAST or TRY_CONVERT is that you can substitute NULL with a different value using CASE. For example, if instead of returning NULL for the dates from the 1400s we could populate with today’s date.

SELECT TRY_CAST('31 Dec 12' AS DATE) AS [First Date],
       TRY_CAST('Dec 12 1776 12:38AM' AS DATE) AS [Second Date],
       CASE
           WHEN TRY_CAST('Dec 12 1400 12:38AM' AS DATETIME) IS NULL THEN
               GETDATE()
           ELSE
               TRY_CAST('Dec 12 1400 12:38AM' AS DATETIME)
       END AS [Third Date],
       TRY_CAST('Number 3' AS INT) AS [Number 3];
GO

The results from the query above will be today’s date. However, this may not be the correct fit for your situation.

Comparing Performance

Now let’s take a minute and compare the performance from TRY_CAST to the standard CAST. I’m going to use one of my favorite tools and that is SqlQueryStress by Adam Machanic. I will test both commands with a total of 100,000 iterations.

First, let’s run CAST. I did change the query so that it would not fail as in my previous examples.

SELECT CAST('31 Dec 12' AS DATE) AS [First Date],
       CAST('Dec 12 1776 12:38AM' AS DATE) AS [Second Date],
       CAST('Dec 12 1776 12:38AM' AS DATETIME) AS [Third Date],
       CAST('1010' AS INT) AS [Number 3];
GO

I ran this three times, taking about seven seconds each time. I waited till SQL cleared all the sessions before running each attempt.

When using TRY_CAST, I did leave in the date from the 1400s.

SELECT TRY_CAST('31 Dec 12' AS DATE) AS [First Date],
       TRY_CAST('Dec 12 1776 12:38AM' AS DATE) AS [Second Date],
       TRY_CAST('Dec 12 1400 12:38AM' AS DATETIME) AS [Third Date],
       TRY_CAST('1010' AS INT) AS [Number 3];
GO

You can see that there was not a massive difference between the two.

I performed a test not included here on a table with about a million rows of dates and found comparable results between the two functions.

Conclusion

In this SQL tutorial, I demonstrated three ways to overcome data type conversion errors with Transact-SQL code for a SQL database. We looked at some examples with TRY_CAST, TRY_CONVERT, and TRY_PARSE. I mentioned situations where you may want to incorporate them. The primary one that saved me time is when generating reports that may not contain the cleanest data. Out of habit, I stick with TRY_CAST because it was the first one I used. I also briefly touched on the performance differences between CAST and TRY_CAST. Over the years, I have not noticed a big performance difference. I invite you to try them if you haven't. I would be interested to hear how you have incorporated them, please leave your comments below.

Next Steps

Joe Gavin created an informative tip covering everything related to data types in SQL Server. I highly recommend you check it out.
Would you like to learn more about SqlQueryStress? Please check out the wiki on the official GitHub page. Brent Ozar also has an excellent article about the free tool.
Please check out this tip by Koen Verbeeck for a comprehensive overview of the CASE statement.

Resources

Community

Subscribe

SQL CONVERT, CAST, TRY_CAST, TRY_CONVERT and TRY_PARSE Examples