New conversion functions in sql server 2012

SQL Server introduced three new conversion functions to convert an expression to another data type, in this post we will see those functions and their usage

Conversion Functions

  • PARSE
  • TRY_PARSE
  • TRY_CONVERT

PARSE: This function parse the value and return then result, relies on CLR. It will take some performance overhead. Syntax

PARSE (string_value AS data_type [ USING culture ] )

Parse function takes three parameter

  1. string_value - The expression which needs to be parsed.
  2. Data_type - Output data type, e.g. numeric, datetime etc.
  3. CULTURE - A language (English, Japanese, Spanish, Danish, French etc.)

Example:

SELECT CAST(’05/02/2013' AS datetime) AS [By-CAST-Function]
GO
SELECT CONVERT(datetime, ’05/02/2013') AS [Bu-CONVERT-Function]
GO
SELECT PARSE(’05/02/2013' AS datetime USING ‘en-US’) AS [By-PARSE-Function]
GO

Result from all will be same, so why we need a new function PARSE which will give the same result as CAST and Convert? Yes, there is reason, suppose you are using a culture which stores date time in DD/MM/YYYY format then it will quite handy to get result in any other culture format say MM/DD/YYYY.

TRY_PARSE: is similar to PARSE() with only difference is that it returns NULL if the conversion is not possible.

Syntax:

 TRY_PARSE (string_value AS data_type [ USING culture ] )

It also accept three parameters same as Parse function

Example:

SELECT PARSE(’100.50' AS datetime2 USING ‘en-US’) AS [By-PARSE-Function]
GO
SELECT TRY_PARSE(’100.50' AS datetime2 USING ‘en-US’) AS [By-PARSE-Function]
GO

Result:
By-PARSE-Function
-------------------------
Msg 9819, Level 16, State 1, Line 1
Error converting string value ’100.50' into data type int using culture 'en-us'.
By-PARSE-Function
------------------------
NULL

TRY_CONVERT: it is similar to to CONVERT function but it first check if conversion is possible or not, if possible then convert to desired data type otherwise return null.

Syntax:

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

It takes three parameter

  1. data_type – The data type into which to cast expression.
  2. expression – The value to be cast.
  3. style – Optional integer expression that specifies how the TRY_CONVERT function is to translate expression.

Example: Try to convert 30 February 2013 to 103 date format

 CONVERT(datetime, '02/30/2013', 103) AS [By-Convert]  -- return empty string
TRY_CONVERT(datetime, '02/30/2013', 103) AS [By-Try-Convert]  -- return NULL
OUTPUT
By-Convert
-----------------

By-Try-Convert
------------------
NULL
Hamden Process manager with a reputed organization, Fond of learning new features and technology related to C#, ASP.Net, SQL Server, MVC etc.I like to help others, if I can
  • sql server
By Hamden On 15 May, 13  Viewed: 455

Other blogs you may like

Adding identity to an existing column in SQL Server

We can not directly change a column to identity columns, say we have a primary key column or any normal column in our table and want to make it identity column then we cannot do it directly by altering the table but there are only two way to change column to identity column 1. Create a new... By Ali Adravi   On 02 May 2013  Viewed: 1,096

some useful sql tricks

There are many ways which can help us to save our time to write some repeated code or to do some repeated action, in this post we will see some very common code which we need very frequently in our development life. I am not saying that after this post you will save your half say work every day but... By Ali Adravi   On 31 Mar 2013  Viewed: 936

Get precision and decimal value in SQL Server by using PARSENAME

In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty   On 26 Mar 2013  Viewed: 3,394

Available wildcards in sql server with examples

To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D   On 25 Feb 2013  Viewed: 678

Concatenate multiple columns in SQL Server with NULL value

When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi   On 13 Feb 2013  Viewed: 12,778