Data validation using TRY_CONVERT function

One of the T-SQL functions introduced in SQL Server 2012 is TRY_CONVERT. It is used to convert a value into a specific data type.

A simple example is

SELECT TRY_CONVERT(DATETIME,'20000101')

The result is 2000-01-01 00:00:00.000

This function can also be used to validate the data

Let us consider the following dataset

create table #t (data varchar(100))
insert into #t (data)
select '20000101' union all
select '20009' union all
select '19-feb-2012' union all
select '200.45' union all
select 'test' union all
select '11.11.2007'

1 Find out only valid dates

select data from #t
where try_convert(datetime,data) is not null

The result is

data
--------------
20000101
19-feb-2012
11.11.2007

2 Find out only integers

select data from #t
where try_convert(int,data) is not null

The result is

data
--------------
20000101
20009

3 Find out only numbers

select data from #t
where try_convert(float,data) is not null

The result is

data
-------------
20000101
20009
200.45

Advertisements

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