T-SQL 101: 84 Avoiding data type conversion errors with TRY_CAST, TRY_CONVERT and TRY_PARSE

In a previous post, I showed how to use CAST and CONVERT. What I didn’t mention before though, is what happens when the conversion will fail. If I try to convert the string ‘hello’ to an int, that just isn’t going to work. Of course, what does happen, is the statement returns an error.  Same thing happens if I try to convert the 30th February 2016 to a date. There aren’t 30 days in February. Again, an error will be returned.

SQL Server 2012 though, added the ability to try to do a conversion, if it works, return the value, and if it fails, just return NULL. And so we got three versions of this:

TRY_CAST

TRY_CONVERT

TRY_PARSE

You can see in the example, that the first string isn’t a valid date, so it returns NULL. The second one is valid, so we get back the converted value.

Now a common mistake that people make when writing T-SQL, is to write code in this type of format:

SELECT CAST(happened_when AS date)
FROM dbo.Events
WHERE ISDATE(happened_when) = 1;

The problem with this, is that you might still get errors saying that happened_when couldn’t be converted to a date. You’d think the WHERE clause would be applied first and would avoid that. But that might not happen.

Now you can deal with this in a robust way, by writing:

SELECT TRY_CAST(happened_when AS date) 
FROM dbo.Events 
WHERE ISDATE(happened_when) = 1;

Even better, this would probably optimize simpler by just writing:

SELECT TRY_CAST(happened_when AS date) 
FROM dbo.Events 
WHERE TRY_CAST(happened_when AS date) IS NOT NULL;

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2021-02-10