Problem
TRY_CONVERT() is a very handy SQL Server function that can be used to “hide” issues with data – usually a design problem, where a column has been created using the wrong data type, or is overloaded to accept multiple data types. Sometimes you can fix the design, and other times it’s beyond your control. While you can use TRY_CONVERT() to work around a bad data type, you can only do so in SQL Server 2012 or later.
Solution
It has come up a few times out on the forums and on Twitter where someone needs the functionality of TRY_CONVERT(), but they’re still running on SQL Server 2005, 2008, or 2008 R2. There is no hope that TRY_CONVERT() will get back-ported to these versions, so users need to find a workaround.
SQL Server TRY_CONVERT Function
What is TRY_CONVERT(), deep down? It’s just a CASE expression. You pass in a value, if you can successfully convert it to the target data type, you return it, otherwise you return NULL. Here is a scenario where you might have seen it; you’re storing dates as strings, and someone has put a bad date in there:
CREATE TABLE #Dates<br />(<br /> TheDate char(8)<br />);<br /><br />INSERT #Dates(TheDate) VALUES<br />('20100101'), -- ok<br />('20121231'), -- ok<br />('20140231'); -- bogus
The inserts all work, of course, because there is no validation of whether the string is a valid date. Of course if you run a query like this, then you will see the failure:
SELECT DATEADD(DAY, 1, TheDate) FROM #Dates;
When the query hits the third row, it bombs, as you would expect:
Msg 242, Level 16, State 3
The conversion of a varchar data type to a datetime
data type resulted in an out-of-range value.
In SQL Server 2012 or later, you could simply write:
SELECT DATEADD(DAY, 1, TRY_CONVERT(date, TheDate)) FROM #Dates;
This would yield:
2010-01-02
2013-01-01
NULL
But on SQL Server 2008, you would get an error message that the function is not valid. So, instead, we can write our own CASE expression around it.
SELECT DATEADD(DAY, 1, <br /> CASE WHEN ISDATE(TheDate) = 1 <br /> THEN CONVERT(date, TheDate) END)<br />FROM #Dates;
Creating Our Own Try Convert SQL Server Functions
It is not convenient to start littering all of our queries with this bulky CASE expression, so instead we can write our own handler function:
CREATE FUNCTION dbo.TryConvertDate<br />(<br /> @value nvarchar(4000)<br />)<br />RETURNS date<br />AS<br />BEGIN<br /> RETURN (SELECT CONVERT(date, CASE <br /> WHEN ISDATE(@value) = 1 THEN @value END)<br /> );<br />END<br />GO
The downside, clearly, is that you need a different function for each data type. There is also this little issue that testing whether something can be converted to something else becomes complicated with certain data types. Even integers are a bit more complex than they should be, since we have real trust issues with the ISNUMERIC() function:
SELECT ISNUMERIC('.'); -- 1<br />SELECT CONVERT(int, '.'); -- fails with the same conversion error
So we have to be a little more clever for integers, and I like to just ensure that there is no character other than 0-9 (and the negative symbol, -), that the length is 11 digits or less, and once those return true, that converting to a bigint still yields a number within the integer range. That looks like this:
CREATE FUNCTION dbo.TryConvertInt<br />(<br /> @value nvarchar(4000)<br />)<br />RETURNS int<br />AS<br />BEGIN<br /> RETURN (SELECT CONVERT(int, <br /> CASE WHEN LEN(@value) <= 11 THEN<br /> CASE WHEN @value NOT LIKE N'%[^-0-9]%' THEN<br /> CASE WHEN CONVERT(bigint, @value) BETWEEN -2147483648 AND 2147483647 <br /> THEN @value <br /> END <br /> END <br /> END));<br />END<br />GO
You could get slightly more precise to push rejections further up the stack; for example, you could check that (the length is <= 10) or that (the length is <=11 and the first character is a negative symbol). But this should filter out violations in either case. It might go too far the other way; for example, maybe you want something like . or 213. to succeed.
It gets more complicated from there; uniqueidentifier is definitely an interesting one, but the basic pattern remains the same. The length of the string must be exactly 36 and it must be the right sequence of characters from A-F, 0-9, and dashes:
CREATE FUNCTION dbo.TryConvertUniqueidentifier<br />(<br /> @value nvarchar(4000)<br />)<br />RETURNS uniqueidentifier<br />AS<br />BEGIN<br /> RETURN (SELECT CONVERT(uniqueidentifier,<br /> CASE WHEN LEN(@value) = 36 THEN<br /> CASE WHEN @value LIKE<br /> '[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'<br /> + '[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'<br /> + '-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'<br /> + '-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'<br /> + '-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'<br /> + '-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'<br /> + '[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'<br /> + '[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'<br /> THEN @value END<br /> END)<br /> );<br />END<br />GO
After Upgrading SQL Server Easy Find and Replace
I could go on, but again, the patterns remain pretty similar. One nice thing about using this naming convention and format is that it becomes easy to search and replace when you do finally get to move away from SQL Server 2008. Just replace all instances of dbo.TryConvertDataType( to TRY_CONVERT(DataType,.
In my next tip, I’ll talk about a way to do this more dynamically and generically, in the case where you only have to deal with a single value at a time. You could still use these functions, of course, but you can handle the conversion in a more clever way, too.
Next Steps
Read on for related tips and other resources:
