Simulating TRY_CONVERT() in SQL Server 2008

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


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.


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:

  TheDate char(8)

('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:


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.

  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)
    WHEN ISDATE(@value) = 1 THEN @value END)

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:

  @value nvarchar(4000)
    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

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
  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]'
    THEN @value END

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:

Last Updated: 2018-04-02

get scripts

next tip button

About the author
MSSQLTips author Aaron Bertrand 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,, and also blogs at

View all my tips

Comments For This Article


Recommended Reading

Generate a parameter list for all SQL Server stored procedures and functions

Validate Integer and Decimal Values in SQL Server

Auto Generate Create Table Script Based on SQL Server Query

Four ways to improve scalar function performance in SQL Server

SQL Server User Defined Function Example

get free sql tips
agree to terms