Conversion functions in SQL Server 2012

data conversion

Conversion functions  in SQL Server 2012

 

Before starting with “Conversion functions in SQL Server 2012” we’ll have a look into “Why data conversion needed?”

Does SQL Server can’t do it automatically?

Yes! SQL server can do data conversion internally which is called implicit data conversion.

Implicit Data Conversion:

While evaluating any SQL expressions SQL Server database engine implicitly converts data depends on the “Datatype Precedence”. “Binary” data type is the least precedence and “User defined data type” is the highest precedence.

For example consider the below query

Here 10 is an integer and ‘10’ is a string. If we run the above statement it gives output as 20

While executing the statement SQL Server compares the data type precedence between the data types INT and VARCHAR. As INT data type is having higher precedence than VARCHAR it implicitly converts the string ‘10’ to integer 10 and hence the result would be 20.

Now we’ll see one more example as below

Here GETDATE() returns DATETIME data type and 10 is an integer. Since DATETIME is higher precedence than INTEGER it adds 10 days to the current date and returns the result as below.

SQL Server implicitly converts all lower data type values to the higher data type values in an expression. Sometimes implicit conversions may lead to performance overhead. To take the control of this conversion we have to use conversion functions explicitly.

Explicit Conversion:

Below are the conversion functions available in SQL Server 2012.

  • CAST

  • CONVERT

  • PARSE

  • TRY_CAST

  • TRY_CONVERT

  • TRY_PARSE

CAST AND CONVERT: As we know that these are the functions and can be used for converting data across data types.  Functionality is similar for these two functions. Major difference between CAST and CONVERT is; CONVERT supports an extra feature called “style”. We can also mention the style of the converted data. It helps us when dealing with DATETIME datatype values.

As a traditional way of converting, we clearly know how to use these functions.

Now we’ll see how these are differentiating for “DATETIME” data values.

Here “SYSDATETIME()” is a system function to get the current date with time.  “106” is the format style in which the data must be converted.

CONVERT function will give more flexibility in handling the date related data types.

Please check here to know more about these functions.

PARSE:

It is also a string conversion function introduced in SQL Server 2012. It converts string to other datatypes. With this conversion function we can indicate the culture in which data can be converted. The culture can be any culture supported by .Net framework. Also PARSE function tries it’s best in converting strings to the intended datatypes.

It is a non SQL Server native function and depends on dotnet CLR hence there would be some performance overhead. Try to use CAST and CONVERT functions wherever possible.

Let’s have a look at few examples.

PARSE function can be more efficient in handling DATETIME data values. It performs it’s best in converting data.

CONVERT/CAST function requires the input to be specified properly, convert fails if the input is not in an acceptable format whereas PARSE can perform well in this situations. Below are the examples.

The CONVERT and CAST fail’s in converting string “WEDNESDAY Jul 03 2013” to datetime.

Now we’ll check the same conversion operation with PARSE

Please have a look at here for more details.

TRY_CAST, TRY_CONVERT, TRY_PARSE:

The difference between the functions without the TRY and their counterparts with the TRY is that those without the TRY fails if the value isn’t convertible, whereas those with the TRY return a NULL in such a case.

Let’s have a look at below example to know differences between with and without TRY.

Now we’ll try some examples with “TRY_”

Please check the below links to know more about these functions.

TRY_CAST

TRY_CONVERT

TRY_PARSE

Posted in SQL Development | Tagged , , , , , , , , , | Leave a comment

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz