Let's learn Try_Convert function of SQL Server 2012 (Denali)

Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 4388 red flag

In this article, we will look into the Try_Convert function of SQL Server 2012 (Denali).


Sql Server 2012 has brought a lot of new functions for the T-Sql developers. One among those functions is the Try_Convert function. In the earlier versions of SQL, it was not possible to know before hand if a casting is possible from one type to another unless we encounter some error confirming about the same.For example, let us try with the below statement

Select CONVERT(int,'This is a string and will fail if you try to convert to int')

A expected it failed with the below error message

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value 'This is a string and will fail if you try to convert to int' to data type int.

In order to avoid this kind of situation, we can use the Try_Convert function


It transforms the source data into the target type if the conversion is possible else null.


Try_Convert (Target Data Type, expression [ , style ])


Target Data Type=> The target datatype

Expression => The value to be cast

Style => It is optional integer expression. It indicates how the function will translate the expression.

Some Example

	Try_Convert(int, 'It is a string') As Result1
	,Try_Convert(int, '100') As Result2	
	,Try_Convert(Date,'18500412') As Result3
	,Try_Convert(DateTime,'Abcd efgh') As Result4
/* Result

Result1	Result2	Result3	Result4
NULL	100	1850-04-12	NULL


Custom Try_Convert function

We can make our own Try_Convert function by using the CLR Function. Here is an attempt to do so

using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
   [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]

    public static SqlBoolean TryConvertFn(string value, string dataType)
        bool status = TryConvert(value, dataType);
        return new SqlBoolean(status);

    /// Function: TryConvert  
    /// bool
    private static bool TryConvert(string val, string key)
        bool flag = true;
	    Dictionary dict = new Dictionary();
		dict.Add("int", typeof(Int32));
		dict.Add("bigint", typeof(Int64));
		dict.Add("datetime", typeof(DateTime));
		dict.Add("numeric", typeof(Decimal));

			switch (dict[key].FullName)
				case "System.Int32":Int32.TryParse(val);break; 
				case "System.Int64":Int64.TryParse(val); break; 
				case "System.DateTime":DateTime.TryParse(val); break;
				case "System.Decimal":DateTime.TryParse(val);break;
			flag = !flag;
        return flag;     

In this case we are maintaining a dictionary object where we are storing some datatypes (just for example sake). Then using the TryParse function, we are trying to convert the source to target type.

N.B.~I have assumed that the readers know atleast the basic of creating a CLR function and installation of the same


SQL Server 2012 (Denali) seems to be very mature and promising and has embeeded with many new functions.In this article we have looked into the Try_Convert function, the benefit of it and how can we make our own CLR based Try_Convert function.Hope the article will be useful.

Thanks for reading

Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Niladri Biswas
Member Level: Platinum
Member Status: Member
Member Since: 10/25/2010 11:04:24 AM
Country: India
Best Regards, Niladri Biswas
Technical Lead at HCL Technologies

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)