Simulating TRY_CONVERT() in SQL Server 2008

By: Aaron Bertrand | Updated: 2018-04-02 | Comments | Related: > Functions User Defined UDF

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
(
  TheDate char(8)
);

INSERT #Dates(TheDate) VALUES
('20100101'), -- ok
('20121231'), -- ok
('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, 
  CASE WHEN ISDATE(TheDate) = 1 
  THEN CONVERT(date, TheDate) END)
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
(
  @value nvarchar(4000)
)
RETURNS date
AS
BEGIN
  RETURN (SELECT CONVERT(date, CASE 
    WHEN ISDATE(@value) = 1 THEN @value END)
  );
END
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
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
(
  @value nvarchar(4000)
)
RETURNS int
AS
BEGIN
  RETURN (SELECT CONVERT(int, 
    CASE WHEN LEN(@value) <= 11 THEN
      CASE WHEN @value NOT LIKE N'%[^-0-9]%' THEN
        CASE WHEN CONVERT(bigint, @value) BETWEEN -2147483648 AND 2147483647 
             THEN @value 
        END 
      END 
    END));
END
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
(
  @value nvarchar(4000)
)
RETURNS uniqueidentifier
AS
BEGIN
  RETURN (SELECT CONVERT(uniqueidentifier,
    CASE WHEN LEN(@value) = 36 THEN
    CASE WHEN @value LIKE
       '[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
    +  '[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
    + '-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
    + '-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
    + '-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
    + '-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
    +  '[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
    +  '[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
    THEN @value END
    END)
  );
END
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:

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2018-04-02