TRY_CONVERT is a new function in SQL Server Denali CTP3, TRY_CONVERT enables you to test if a value can be converted to a specific data type, TRY_CONVERT returns a value cast to the specified data type if the cast succeeds; otherwise, TRY_CONVERT returns null.

Here is what Books On Line has to say about TRY_CONVERT

TRY_CONVERT takes the value passed to it and tries to convert it to the specified data_type. If the cast succeeds, TRY_CONVERT returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error.

Arguments

data_type [ ( length ) ]
The data type into which to cast expression.

expression
The value to be cast.

style
Optional integer expression that specifies how the TRY_CONVERT function is to translate expression.

style accepts the same values as the style parameter of the CONVERT function.

The range of acceptable values is determined by the value of data_type. If style is null, then TRY_CONVERT returns null.

Here is what the syntax looks like

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

Let’s take a look how this all works, I will create a table and inserts some values

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create table #test(SomeCol varchar(100))
GO
 
insert #test values('1')
insert #test values('.')
insert #test values('$')
insert #test values('ddd')
insert #test values('---')
insert #test values('000')
insert #test values('123aaa1')
insert #test values('2de')
insert #test values('((((')
insert #test values('20110230')
insert #test values('20110228')
insert #test values('14:58')
insert #test values('16000228')
insert #test values('0.12345678901')
create table #test(SomeCol varchar(100))
GO

insert #test values('1')
insert #test values('.')
insert #test values('$')
insert #test values('ddd')
insert #test values('---')
insert #test values('000')
insert #test values('123aaa1')
insert #test values('2de')
insert #test values('((((')
insert #test values('20110230')
insert #test values('20110228')
insert #test values('14:58')
insert #test values('16000228')
insert #test values('0.12345678901')

Now, I will try to convert the values in the table to various data types

T-SQL
1
2
3
4
5
6
7
8
9
select SomeCol,
       TRY_CONVERT(float,SomeCol) as float,
       TRY_CONVERT(date,SomeCol) as date,
       TRY_CONVERT(datetime2,SomeCol) as datetime2,
       TRY_CONVERT(datetime,SomeCol) as datetime,
       TRY_CONVERT(time,SomeCol) as time,
       TRY_CONVERT(numeric(30,10),SomeCol) as numeric,
       TRY_CONVERT(int,SomeCol) as int
FROm #test
select SomeCol,
	   TRY_CONVERT(float,SomeCol) as float,
	   TRY_CONVERT(date,SomeCol) as date,
	   TRY_CONVERT(datetime2,SomeCol) as datetime2,
	   TRY_CONVERT(datetime,SomeCol) as datetime,
	   TRY_CONVERT(time,SomeCol) as time,
	   TRY_CONVERT(numeric(30,10),SomeCol) as numeric,
	   TRY_CONVERT(int,SomeCol) as int
FROm #test

Here is the result

SomeCol float date datetime2 datetime time numeric int
1 1 NULL NULL NULL NULL 1 1
. NULL NULL NULL NULL NULL NULL NULL
$ NULL NULL NULL NULL NULL NULL NULL
ddd NULL NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL NULL
0 0 NULL NULL NULL NULL 0 0
123aaa1 NULL NULL NULL NULL NULL NULL NULL
2de NULL NULL NULL NULL NULL NULL NULL
(((( NULL NULL NULL NULL NULL NULL NULL
20110230 20110230 NULL NULL NULL NULL 20110230.0000000000 20110230
20110228 20110228 2011-02-28 00:00.0 00:00.0 00:00.0 20110228.0000000000 20110228
14:58 NULL 1900-01-01 1900-01-01 14:58:00.0000000 1900-01-01 14:58:00.000 14:58:00.0000000 NULL NULL
16000228 16000228 1600-02-28 1600-02-28 00:00:00.0000000 NULL 00:00.0 16000228.0000000000 16000228
0.12345678901 0.12345678901 NULL NULL NULL NULL 0.123456789 NULL

Pretty neat, as you can see if you for example try to convert 16000228 to a datetime you will get 0 since it falls out of the acceptable datetime range, for datetime2 and date you do get a value back. The conversion to numeric(30,10) also shows that the value is truncated after 10 decimals. This function is pretty handy since you won’t get the conversion errors you would get if you try to convert it with the regular convert function

You can also use CASE or IIF to return if the value can or cannot be converted, below is an example of both

CASE

T-SQL
1
2
3
4
5
6
7
8
9
10
11
SELECT 
    CASE WHEN TRY_CONVERT(float,'bla') IS NULL 
    THEN 'Cast failed'
    ELSE 'Cast succeeded'
END 
UNION
SELECT 
    CASE WHEN TRY_CONVERT(float,'1') IS NULL 
    THEN 'Cast failed'
    ELSE 'Cast succeeded'
END 
SELECT 
    CASE WHEN TRY_CONVERT(float,'bla') IS NULL 
    THEN 'Cast failed'
    ELSE 'Cast succeeded'
END 
UNION
SELECT 
    CASE WHEN TRY_CONVERT(float,'1') IS NULL 
    THEN 'Cast failed'
    ELSE 'Cast succeeded'
END 

——–
Cast failed
Cast succeeded

IIF

T-SQL
1
2
3
SELECT IIF(TRY_CONVERT(float,'bla')IS NULL,'Cast failed','Cast succeeded')
UNION
SELECT IIF(TRY_CONVERT(float,'1')IS NULL,'Cast failed','Cast succeeded')
SELECT IIF(TRY_CONVERT(float,'bla')IS NULL,'Cast failed','Cast succeeded')
UNION
SELECT IIF(TRY_CONVERT(float,'1')IS NULL,'Cast failed','Cast succeeded')

——–
Cast failed
Cast succeeded

Be aware that if you pass in a NULL, then NULL is returned

T-SQL
1
select  TRY_CONVERT( numeric(30,10),null)
select  TRY_CONVERT( numeric(30,10),null)

TRY_CONVERT is something that was long overdue and it will eliminate a lot of issues, no more need for custom IsNumeric and IsInt functions, this one does it all.