1. Handling invalid dates
So far, we have looked at the best case scenario: our input data is perfect and we get back the dates that we expect. In practice, however, we need to be prepared to deal with invalid dates when importing data from flat files or other external sources. This is where the class of error-safe date conversion functions comes in.
2. Error-safe date conversion functions
With SQL Server 2012 and later, we have the ability to convert input strings to dates safely. Using the "unsafe" functions on the left will work just fine with good data, but if we have a single invalid date, these functions will return an error, causing our query to fail. By contrast, the "safe" functions on the right will handle invalid dates by converting them to NULL.
3. When everything goes right
As a quick reminder, the PARSE() function allows us to render an input date using a specific culture.
For example, 01/08/2019 translates to January 8th, 2019 on machines with US locales,
but it becomes August 1st, 2019 on machines in French locales.
4. When everything goes wrong
But let's move on to 01/13/2019.
Now, when we run this script, we get an error message telling us that the database engine could not convert our input string into a valid French date. Because there is no thirteenth month, the engine doesn't have any choice but to fail the query. Notice that even though the parse succeeded for our January 13th result, because the query failed, we get neither back.
5. Doing right when everything goes wrong
If we switch PARSE() to TRY_PARSE(), we can run this query again and this time, the query succeeds.
Because there still isn't a thirteenth month in France, the database engine still has no idea what this date should be. But instead of failing and returning an error, the engine returns NULL and lets us deal with the results.
6. The cost of safety
You may be wondering what the performance impact of using these safe functions is. As we saw earlier, we know that PARSE() is much slower than CAST() or CONVERT(). So what happens when we switch over to the safe functions? It turns out that the safe functions have no discernible performance impact.
7. The cost of safety
CONVERT() and CAST() both converted 240-250 thousand rows per second regardless of whether we used the safe or unsafe version.
8. The cost of safety
And PARSE() lagged behind by more than an order of magnitude. This is because PARSE() uses the Common Language Runtime under the covers, reaching out to Microsoft dot-NET Framework code to translate strings to dates, whereas CONVERT() and CAST() are optimized functions which stay inside the SQL Server database engine confines. So we can see that there is no performance-related reason not to use the safe date conversion functions, and bias our code toward using CONVERT() and CAST() over PARSE() whenever possible.
9. The cost of safety
One other thing to note is that this conversion rate is a linear function of the number of rows, meaning that I will consistently perform 240-250 thousand conversions per second on my test machine, regardless of how many rows might be in the table. Your numbers will almost assuredly be different from mine, but you will see the same pattern even if the magnitudes differ.
10. Let's practice
Now that we have an idea of what the error-safe date conversion functions are and how they work, let's try them out. You will also get a chance to see how these functions perform outside of my test machine.