In my previous post, I discussed about TRY_CONVERT. Today, we will discuss one more excellent conversion function of SQL server namely “TRY_CAST”. Before SQL server 2012, we use “Cast” function very frequently. whenever we need to cast expression from one data type to another data type, we use this function. But there is a problem with this function, if you pass any invalid data to this function and try to cast it into incompatible data type. It will generate an error. TRY_CAST is the solution of this problem.
For Example :

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.

In my previous post, I discussed about PARSE function. In this function, if the data type is not compatible and could not be parsed then this function will generate an error. Now, we have the solution for it namely “TRY_PARSE”. SQL SERVER has introduced this (TRY_PARSE) built in conversion function in 2012. This function is very useful because most of the time due to the incorrect data , your system may generate errors. And if you use this function it will return NULL in case of error.
Lets discuss its syntax , purpose and examples in detail.

TRY_PARSE

Syntax :

TRY_PARSE ( string_value AS data_type [ USING culture ] )

Purpose :
This function tries to parse string value (nvarchar(4000) data type) to datetime or numeric data types. But if it fails to parse, it will return NULL value. We need to provide three parameters to this function string value , data type & culture parameter respectively. String value & data type parameter are mandatory but the culture parameter is optional . As the culture parameter is option so, by default it picks up the culture from the session.It uses the .NET Framework Common Language Run time(CLR). Before using this function, we should also know, that it cost extra overhead in the performance.

SQL SERVER 2012 has a new built in function namely “PARSE”. Lets discuss its syntax , purpose and examples in detail.

PARSE

Syntax :

PARSE ( string_value AS data_type [ USING culture ] )

Purpose :
This function converts the string value (nvarchar(4000) data type) to datetime or numeric data types.
We need to provide three parameters to this function string value , data type & culture parameter respectively. String value & data type parameter are mandatory but the culture parameter is optional . As the culture parameter is option so, by default it picks up the culture from the session.It uses the .NET Framework Common Language Run time(CLR). Before using this function, we should also know, that it cost extra overhead in the performance.

Example-1 : PARSE -Varchar to Numeric

SELECT PARSE('123.45' AS NUMERIC(18,2)) AS Output
--Result
123.45

Example-2 : PARSE – Varchar to Int
Lets use the same example of numeric and cast it to integer.

SELECT PARSE('123.45' AS INT) AS Output
--Result

Msg 9819, Level 16, State 1, Line 1 Error converting string value ‘123.45’ into data type int using culture .Note : The above line of code will generate error because whatever we want to parse should be compatible with the parse data type.

Select Parse('$123.45' as money using 'en-US')
Select Parse('€123,45' AS money using 'de-DE')
--Result of the above statements
123.45

Example-6 : PARSE – Language Setting Instead of Culture

SET LANGUAGE 'French'
Select Parse('26/08/2012' as datetime)
SET LANGUAGE 'German'
Select Parse('26.08.2012' AS datetime2) AS Result
SET LANGUAGE 'Italian'
Select Parse('26-08-2012' as datetime)
--Result of the above statements
2012-08-26 00:00:00.000

Error :
If the string value is invalid to convert it into date time, numeric & any specific culture format, then it will generate an error.

Blog Stats

Follow Blog via Email

Disclaimer

This blog/website is a personal blog/website and all articles, postings and opinions contained herein are my own. The contents of this blog/website are not intended to defame, purge or humiliate anyone should they decide to act upon or reuse any information provided by me. Comments left by any independent reader are the sole responsibility of that person. Should you identify any content that is harmful, malicious, sensitive or unnecessary, please contact me via email (imran@raresql.com) so I may rectify the problem.