udayarumilli.com

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

SELECT 10+'10'

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

SELECT GETDATE()

SELECT GETDATE()+10

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.

SELECT CAST('10' AS INT)+10 AS 'Total'

SELECT CONVERT(INT,10)+10 AS 'Total'

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

SELECT 'OP Completed on: '+CAST(SYSDATETIME() AS VARCHAR(30)) AS 'Msg'

SELECT 'OP Completed on: '+CONVERT(VARCHAR(30),SYSDATETIME(),106) AS 'Msg'

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.

-- Convert string to Int

SELECT PARSE('100' AS INT) AS 'Integer';

-- Convert string to Decimal

SELECT PARSE('100.334' AS DECIMAL(10,3)) AS 'Decimal';

-- Convert string to DateTime using US format

SELECT PARSE('03/07/2013' AS DATETIME USING 'en-US') AS 'DateTime';

-- Convert string to DateTime using INDIA format

SELECT PARSE('03/07/2013' AS DATETIME USING 'en-IN') AS 'DateTime';

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.

-- Let’s try to convert String to datetime using CONVERT

SELECT CONVERT(DATETIME,'WEDNESDAY Jul 03 2013')

GO

-- Let’s try to convert String to datetime using CAST

SELECT CAST('WEDNESDAY Jul 03 2013' AS DATETIME)

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

-- Lets try to convert String to datetime using PARSE

SELECT PARSE('WEDNESDAY Jul 03 2013' AS DATETIME) AS 'Str2Date'

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.

-- When we try to convert a string to INT using CAST

SELECT CAST('T100' AS INT) AS 'With CAST';

GO
-- When we try to convert a string to INT using CONVERT

SELECT CONVERT(INT,'T100') AS 'With CONVERT';

GO
-- When we try to convert a string to INT using PARSE

SELECT PARSE('T100' AS INT) AS 'With PARSE';
GO

Now we’ll try some examples with “TRY_”

-- When we try to convert a string to INT using CAST

SELECT TRY_CAST('T100' AS INT) AS 'With CAST';
GO

-- When we try to convert a string to INT using CONVERT

SELECT TRY_CONVERT(INT,'T100') AS 'With CONVERT';
GO

-- When we try to convert a string to INT using PARSE

SELECT TRY_PARSE('T100' AS INT) AS 'With PARSE';
GO

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

TRY_CAST

TRY_CONVERT

TRY_PARSE