Home > Denali, TSQL > SQL Server Denali– TRY_CONVERT() conversion function

SQL Server Denali– TRY_CONVERT() conversion function

TRY_CONVERT is new conversion function in SQL Server Denali. Unlike the CAST and CONVERT functions, TRY_CONVERT allows us to test whether it is possible to convert a value to specific data type.

TRY_CONVERT converts passed value to the specified data type, if conversion isn’t possible null is returned. But, if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error.

Syntax: TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )
style accepts the same values as the style parameter of the CONVERT function.

Let’s see how it works.

SELECT TRY_CONVERT(datetime, '2010-12-13') AS Date1,
TRY_CONVERT(datetime2, '20101203') AS Date2,
TRY_CONVERT(datetime, '21:15') AS  Date3,
TRY_CONVERT(time, '2010-12-13') AS Time1,
TRY_CONVERT(time, '21:15') AS Time2

DECLARE @a as datetime
SET @a = '2011-07-25 21:15:31.120'

SELECT TRY_CONVERT(date, @a),
TRY_CONVERT(time, @a),
TRY_CONVERT(varchar(10), @a,104),
TRY_CONVERT(varchar(10), @a,108),
TRY_CONVERT(numeric(18,6), @a),
TRY_CONVERT(float, @a),
TRY_CONVERT(datetime,TRY_CONVERT(float, @a))

Pre-Denali, conversion of value ‘2010-12-32’ to datetime will raise an error.

SELECT CONVERT(datetime, '2010-12-32')

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

In Denali, with TRY_CONVERT we get NULL. But if we try a conversion that is explicitly not permitted, TRY_CONVERT fails with an error.

DECLARE @a XML
SET @a = '<a>1</a>'
SELECT TRY_CONVERT(int, @a)

Msg 529, Level 16, State 2, Line 3
Explicit conversion from data type xml to int is not allowed.

To see how TRY_CONVERT works with numbers, I will create table, insert some values and try to convert values from the table to a various number data types.

CREATE TABLE #T1 (a varchar(20))
GO
INSERT INTO #T1
VALUES ('10'),('12.4'),('Value'),('12,6'),('-5'),('3d3'),(CHAR(9))/*Tab*/
,(CHAR(10))/*new line*/,('+'),('-'),('$'),(CHAR(32))/*space*/,('2.31e-3')
GO
SELECT a,
TRY_CONVERT(int, a) AS ASInteger,
TRY_CONVERT(numeric(8,2), a) AS ASNumeric,
TRY_CONVERT(float, a) AS ASFloat,
TRY_CONVERT(money, a) AS ASMoney,
FROM #T1

As you can see, if we try to convert signs “+” ,“-“ and space to integer we will get 0, but they can’t be converted to numeric data type. We can easily find all integer values in the table.

-- Everything that can be parsed to int
SELECT a,
TRY_CONVERT(int,a) IntegerValues
FROM #t1
WHERE TRY_CONVERT(int,a) IS NOT NULL

-- without signs "+","-" and space
SELECT a,
TRY_CONVERT(int,a) IntegerValues
FROM #t1
WHERE TRY_CONVERT(int,a) IS NOT NULL
AND a NOT IN ('+','-',CHAR(32))

TRY_CONVERT is excellent for data type conversion and will eliminate a lot of issue. No more need for custom IsInt or IsNumeric function.

Advertisements
Categories: Denali, TSQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: