Why include another conversion function– PARSE () and TRY_PARSE ()

PARSE is new T-SQL and relies on the .NET framework Common language Runtime (CLR). This function same as CAST and CONVERT with the additional parameter called culture.

Syntax:

There are two required arguments and one optional argument that are passed into the function.

• String_value: nvarchar(4000) value representing the formatted value to parse into the specified data type.
• Data_type: Literal value representing the data type requiested for the result.
• Culture: (Optional) string that identifies the culture in which string_value is formatted.

here is a another new function introduce with SQL 2012:  TRY_PARSE()

The different between PARSE() and TRY_PARSE()  is, if we get one or more incorrect values, PARSE() function will through an error.  However, if we use TRY_PARSE() function, it will not throw error but will return the result as NULL.

Why include another conversion function? The PARSE over CAST or CONVERT is the ability of convert the string to a numeric or date and time value without have to use string functions to manipulate the input. For example, if you take date, based on region position of the day, month, and year may change. The following example clearly illustrates the usefulness. Even first two string values are different; due to culture option output is same. In second and third query both value are same but the output different, due to the different culture.

us_english : en-US

British : en-GB

I also would write other conversion functions as well:

Syntax for the CAST and CONVET

Note: TRY_CONVERT() also new function introduced with SQL Server 2012 version.

References:

http://technet.microsoft.com/en-us/library/hh213316.aspx

http://msdn.microsoft.com/en-us/library/ms187928(SQL.90).aspx

Cheers!