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: 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
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
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
|
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
|
| By Hamden On 15 May, 13 Viewed: 455 |
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
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
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
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
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