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:
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:
|
1 2 3 |
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:
|
1 2 3 |
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:
|
1 2 3 |
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.