SQL Server Portal

In SQL server, we use “Convert” function very frequently.Whenever, we need to convert one expression data type to another, we use this function. But there is a problem with this function, if you pass any invalid data to this function and try to convert it. It will generate an error and can impact you application.
For Example :

Select Convert (datetime2,'2012-08-32')
--Result

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

To resolve this issue, Microsoft has introduced a new function in SQL SERVER 2012, namely “TRY_CONVERT”.
It requires three parameters data type, expression & style. Data type and expression are mandatory but the style is optional.
Lets discuss this function syntax , purpose and examples in detail.

TRY_CONVERT

Syntax :

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

Purpose :
This function converts expression from one data type to another data type and if it fails to convert it will return a NULL value as a results.

Example-1 : TRY_CONVERT – Passed

Select TRY_Convert(datetime,'2012-09-30',101) as [Result]
--Result
2012-09-30 00:00:00.000

Example-2 : TRY_CONVERT – Failed
If try _convert Failed,  it will return NULL value.
Lets say we don’t have 31 days in September. Now, try to convert it

Select TRY_Convert(datetime,'2012-09-31',101) as [Result]
--Result
NULL

Example-3 : TRY_CONVERT WITH IIF STATEMENT
In this example , we will try to convert a valid and invalid expression to Integer.

Select IIF(Try_Convert(int, 'test') is NULL , 'Invalid Integer', 'Valid Integer') as [Result]
--Result
Invalid Integer

Select IIF(Try_Convert(int, 2) is NULL , 'Invalid Integer', 'Valid Integer') as [Result]
--Result
Valid Integer

Example-4 : TRY_CONVERT WITH CASE STATEMENT
In this example , we will try to convert a valid and invalid expression to Integer.

Select (Case When Try_Convert(datetime2,'2012-08-22') is NULL Then 'Invalid Date format' else 'Valid Date format' end) as [Result]
--Result
Valid Date format

Select (Case When Try_Convert(datetime2,'2012-08-32') is NULL Then 'Invalid Date format' else  'Valid Date format' end) as [Result]
--Result
Invalid Date format

Reference : MSDN

Read Full Post »