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
[…] Comments « SQL SERVER 2012 -Conversion Function -TRY_CONVERT […]
very helpful dada…thanks
[…] CONVERT to convert varchar to float but in SQL Server 2012, you can do it with just one function TRY_CONVERT. Let me create a sample to explain […]
[…] : I developed the given below solution using TRY_CONVERT() (One of the new conversion functions shipped with SQL Server […]
[…] solution, we need to use one of the new conversion functions shipped with SQL Server 2012 namely TRY_CONVERT […]
[…] solution, we need to use one of the new conversion functions shipped with SQL Server 2012 namely TRY_CONVERT […]
[…] TRY_CONVERT is one of the new conversion functions shipped with SQL Server. This function converts expression from one data type to another data type. The beauty of this function is that if it fails to convert, it returns NULL value as a results and due to this functionality, it has an edge over CONVERT function. I started using this function wherever conversion required since SQL Server 2012, however, I received an error message this morning when I was trying to use TRY_CONVERT function in SQL Server 2014 as shown below. […]