PARSE, TRY_PARSE and TRY_CONVERT Functions in SQL Server 2012

PARSE, TRY_PARSE and TRY_CONVERT Functions in SQL Server 2012

After giving the understanding of new introduced system stored procedure in SQL Server 2012 “sp_describe_first_result_set”, once again I come up with few new exciting system function of SQL Server 2012.

Let us have a lookup of each function whose name given in the title:

PARSE :

PARSE function is works similarly like COVERT function of SQL Server. You can provide string value in PARSE function to get it converted to string, numeric, datetime as per your need. PARSE function tries its best to convert provided string to your desired datatype but if it won’t able to convert it, PARSE function will return the error. Let us see small demo of PARSE function.

DECLARE @StringVal VARCHAR(20)
SET @StringVal='13-OCT-2012 10:00:00'
SELECT PARSE(@StringVal AS DATETIME)
SET @StringVal='13OCT2012 10:00:00'
SELECT PARSE(@StringVal AS DATETIME)
SET @StringVal='2012OCT13 10:00:00'
SELECT PARSE(@StringVal AS DATETIME)

We have not provided date-time in proper format in @StringVal variable though PARSE function will convert it successfully. Have a look at screen capture below:

Now let us try something which PARSE function can’t convert so that we shall be greeted with the error message.

DECLARE @StringVal VARCHAR(20)
SET @StringVal='32-OCT-2012 10:00:00'
SELECT PARSE(@StringVal AS DATETIME)

This is something PARSE function can’t convert as 32 is a wrong date. We shall meet the error something like this:

Msg 9819, Level 16, State 1, Line 4

Error converting string value ’32-OCT-2012 10:00:00′ into data type datetime using culture ”.

Confirm this from given screen capture.

For more details about PARSE function and possible conversion along with different parameter, check MSDN.

TRY_PARSE :

TRY_PARSE function works exactly same as PARSE function the only difference is, if PARSE function can’t convert the value, it will throw an error whereas TRY_PARSE function will return the value NULL rather than giving error.

DECLARE @StringVal VARCHAR(20)
SET @StringVal='13-OCT-2012 10:00:00'
SELECT TRY_PARSE(@StringVal AS DATETIME)
SET @StringVal='13OCT2012 10:00:00'
SELECT TRY_PARSE(@StringVal AS DATETIME)
SET @StringVal='2012OCT13 10:00:00'
SELECT TRY_PARSE(@StringVal AS DATETIME)

We shall get the same result as we have received in PARSE function because all values could be converted to datetime format. Confirm the same from following screen capture

Let us try something which can’t be parsed so that we shall see whether errors coming or NULL with TRY_PARSE function.

DECLARE @StringVal VARCHAR(20)
SET @StringVal='32-OCT-2012 10:00:00'
SELECT TRY_PARSE(@StringVal AS DATETIME) 

Again, its 32 in date so it can’t be parsed but TRY_PARSE function won’t give us error rather it will display NULL, confirm the same from following screen capture:

For more details about TRY_PARSE function and possible conversion along with different parameter, check MSDN

TRY_CONVERT

TRY_CONVERT is completely same as CONVERT function in SQL Server which we used to use since long. The only difference is TRY_CONVERT returns the NULL if conversion fails.

DECLARE @StringVal VARCHAR(20)
SET @StringVal='13-OCT-2012 10:00:00'
SELECT TRY_CONVERT(DATETIME,@StringVal)
SET @StringVal='13OCT2012 10:00:00'
SELECT TRY_CONVERT(DATETIME,@StringVal)
SET @StringVal='2012OCT13 10:00:00'
SELECT TRY_CONVERT(DATETIME,@StringVal)  

All values will be converted and result with remain same as we had with PARSE and TRY_PARSE functions. Confirm the same with following screen shot.

Now let us again try to convert something which is not possible, we shall not receive any error but we will receive the NULL in that case again.

DECLARE @StringVal VARCHAR(20)
SET @StringVal='32-OCT-2012 10:00:00'
SELECT TRY_CONVERT(DATETIME,@StringVal) 

We shall have NULL by above conversion, confirm with following screen capture

Enjoy SQL Server 2012

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles.

8 thoughts on “PARSE, TRY_PARSE and TRY_CONVERT Functions in SQL Server 2012

  1. Pingback: New DATE TIME functions in SQL Server 2012

  2. Pingback: LAST_VALUE and FIRST_VALUE function in SQL Server 2012

  3. Pingback: CUME_DIST and PERCENT_RANK new analytical functions in SQL Server 2012

  4. Pingback: LEAD and LEG Analytic functions in SQL Server 2012

  5. Pingback: Useful functions in SQL Server

  6. Pingback: New Logical Function CHOOSE in SQL Server 2012

Comments are closed.