TRY_CONVERT in Sql Server Denali (MSSQL2012)

TRY_CONVERT: As the name suggests converts data to specified datatype if succeed then returns result else returns null.

Let’s Explore It

Select '2011SEP1 To DateTime' As Data , TRY_CONVERT(DATETIME,'2011SEP1') As Value
Select '2011-SEP-41 To DateTime' As Data , TRY_CONVERT(DATETIME,'2011-SEP-41') As Value
Select 'Kuldeep To Int' As Data , TRY_CONVERT(Int,'Kuldeep') As Value
Select '125.45 To Int' As Data , TRY_CONVERT(Int,'125.45') As Value
Select '1 To XML' As Data , TRY_CONVERT(XML,'1') As Value
Select '1 To Decimal(12.4)' As Data , TRY_CONVERT(Decimal(12,4),1) As Value

Output:

It is clear from above that function is trying to convert the source value to target datatype and return value if it can convert else it return null. This function can be used to do data validation. Create Table With Following Script.

Create Table InvalidData(Id int Identity(1,1),EmpID Varchar(30),Commission Varchar(30),EntryDate Varchar(50))
Go

Insert Into InvalidData Values
('E001','12.50','01-01-2011'),('E004','8 PERCENT','AUG 2011'),('E002','16 OF TOTAL','2011-JAN'),
('E006','9.50','JAN-11'),('E0341','23.0','20 FABUARY'),('E067','015','MAR,16,2011'),
('E011','001','31/04/2011'),('E021','12%','12/31/2011'),('E090','$7.5','16 APR 11')
Go

Select * From InvalidData
Go

Output;

As you can see Table with invalid data. I know it very bad design of table but one may get this result or table when you import data from different sources may be a text file or CSV and etc.

First thing you noticed is that EntryDate Field is Varchar and it has lots of combination of data describing Date. another column Commission has similar nature having percentage defined in different way.

Currently we Make use of IsDate or IsNumeric but these functions only tell whether data is Date or numeric. Here TRY_CONVERT can be very usefull see following.

Select Id,EmpId,Commission,TRY_CONVERT(Decimal(10,2),Commission ) as Valid_Commission,
EntryDate,TRY_CONVERT(DateTime,EntryDate) as Valid_DateTime
From InvalidData
Go

Output :

So in above output you can clearly see that invalid Commission and Entry Date has been converted as null by TRY_CONVERT Function. Isn’t It Nice Function ? This function really going to help in ETL and Data Mining.