RSS

New Conversion Function In SQL Server 2012

11 Jan

Conversion Function

1- Parse()  function (can convert any string value to Numeric or Date/Time format.)

–Example1: Converting String to INT
SELECT PARSE(‘100.000’ AS INT) AS ValueInt
 select parse()

 –Example2: Converting String to Date/Time
SELECT PARSE(‘July 30, 2011’ AS DATETIME)
 AS ValueDT
 

2- Try_parse() Function

The TRY_PARSE() function can convert any string value to Numeric or Date/Time format. If the passed string value cannot be converted to Numeric or Date/Time format, it will result to a NULL.

Example1: Converting String to INT
 
— No error
 SELECT PARSE(‘100.000’ AS INT) AS ValueInt;
 SELECT TRY_PARSE(‘100.000’ AS INT) AS ValueInt;
 — Error
 SELECT PARSE(‘sa300.000’ AS INT) AS ValueInt;
 SELECT TRY_PARSE(‘mostafa1985.10’ AS INT) AS ValueInt;

3-  TRY_CONVERT() function

This Function like Convert but convert function if you try to convert string to int it will result Error but TRY_COBVERT it will give you NULL like TRY_PARSE() Function .

—No Error
SELECT CONVERT(INT, ‘500’) AS ValueInt;
 SELECT TRY_CONVERT(INT, ‘500’) AS ValueInt;
—Error
 SELECT CONVERT(INT, ‘m100.000’) AS ValueInt;
 SELECT TRY_CONVERT(INT, ‘m100.000’) AS ValueInt;

Conversion Function – Difference between PARSE(), TRY_PARSE(), TRY_CONVERT()

Diffrence Between pars() and Try_Pars() Function : Parse if try to use it with one or more incorrect values will throw an error However, if you use TRY_PARSE function it will not throw error but will return parse string to  the result as NULL

difference between Convert() and TRY_CONVERT() functions: Convert function try to convert the string and return the value if it can if it can’t it will return Error , However Try_Convert Function it will try to convert the string and return value if it can but if it can’t it will return NULL.

 
1 Comment

Posted by on January 11, 2012 in New Function

 

One response to “New Conversion Function In SQL Server 2012

  1. Motafa Elmasry

    January 13, 2012 at 4:06 PM

    SELECT TRY_PARSE(’11-12-2011′ AS datetime USING ‘en-US’) AS Result
    SELECT TRY_PARSE(’11-12-2011′ AS datetime USING ‘da-DK’) AS Result

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s