Prashanth Jayaram

SQL CAST and SQL CONVERT function overview

November 16, 2018 by

This article is an effort to discuss SQL Cast and SQL Convert functions as a follow-up to previous articles, in which we’ve discussed several SQL tips such as SQL Date, SQL Coalesce, SQL Union, SQL Join, SQL Like, SQL String etc.

Sometimes we need to convert data between different SQL data types. In addition to working with data, there are some built-in functions can be used to convert the data. So let’s take a closer look at the SQL conversion functions SQL CAST and SQL CONVERT in detail.

Introduction

In order to perform operations or comparisons or transformation between data in a SQL Server database, the SQL data types of those values must match. When the SQL data types are different, they will go through a process called type-casting. The conversion of SQL data types, in this process, can be implicit or explicit.

The data type conversion functions commonly used specially to meet the data standards of the target objects or systems. We are in the world dealing with heterogeneous data.

Note: A walk-through of high-level concepts of data management is discussed in this article SQL string functions for Data Munging (Wrangling).

Sometimes the data types of the data need to get converted to other data types for calculations or transformation or processes or to meet destination data formats. For example, when we multiply decimal type data with an integer, the data undergoes internal transformation and implicitly converts an integer to decimal data type and the result is of decimal data.

Implicit and Explicit SQL conversion

When we deal with two values which are same in nature but different data types, behind the scenes, the database engine convert the lower data type values to higher data type before it could go ahead with the calculation. This type is known as an implicit conversion. On the other hand, we have explicit conversions where you either call a SQL CAST or SQL CONVERT function to change the data type. You can refer to the SQL date format Overview; DateDiff SQL function, DateAdd SQL function and more article for examples.

Syntax:

exp
Defines the valid expression argument

datatype
This gives the details of target data type

len
This specified the length of the target data type. This is an optional parameter by default, the length value is set to 30

style
It is an integer value used with the SQL CONVERT function to translate expression to desired output

SQL Convert and Cast data conversion chart

The following conversion chart is a quick reference sheet for data type conversion

SQL Convert and SQL Cast data conversion chart
Reference: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

Example 1: SQL Implicit conversion with numbers

In the following example, two random numbers 5 and 212345 are added and results are displayed.

Implicit conversion results with numbers

We see that the two values are of different data types, the database engine implicitly converted the int value to a bigint before it did the addition. And then it could process the addition operation as they become the same data type.

Example 2: SQL Implicit conversion with characters

In the following example, we’ll add two character data types. Let us create variables @a character data type and assign a value ‘5’ and @b with a value ‘2’.

In the output, first, we get the values of the variables ‘a’ and ‘b’, second and result of the calculation that is both the values together. With the character values are in place, the database engine implicitly decided to concatenate both values and display 52 as the output of the calculation.

Implicit conversion results with characters

Example 3: How to perform an Explicit conversion using SQL Cast

In the following example, we’ll see how to force the conversion from one data type to another. Let us take an above example and convert the input values to numeric type and then add those values.

The SQL CAST function takes two arguments. First, the data we want to process, in this case, the data is ‘5’ and ‘2’ and are of char field, and then how you want to process it, or how we want to CAST it. In this case, you want to CAST it as an integer field.

In the output, you notice that the input character values are converted and also added those values and result is of integer type.

Explicit conversion results using SQL Cast

Example 4: Advanced error handling using TRY_CAST

In the following example, we’ll further dissect the values of data types using SQL cast function and SQL try_cast function.

In the following SQL, the string value ‘123’ is converted to BIGINT. It was able to convert because of the nature of the data. Let us take a look at the second example, the string value ‘xyz’ is used in the expression for the SQL cast function. When you’re doing this, you will see an error stating “Error Converting data type varchar to bigint”. When you’re doing the data analysis with various data sources, we usually get such type of values in the source data. So in order to handle this type of scenarios, you can use the SQL try_cast function. In the third line of SQL, we pass the string as an input value, after execution, we’ve not received an error but end up in getting a NULL value as an output. The NULL value can be further simplified using SQL ISNULL function or SQL coalesce function.

Note: You can refer to the article Using the SQL Coalesce function in SQL Server for more information.

Error handling results from using Try Cast

Example 5: Explained style code in CONVERT functions

The following example converts hiredate field to different available styles of the SQL convert function. The SQL convert function first starts with a data type, and a comma, and then followed by an expression. With Dates, we also have one other argument that we can supply, that’s called a SQL style code. You can get a listing of these in the article CAST and CONVERT (Transact-SQL) technical documentation.

Let’s go ahead execute the aforementioned SQL. Now we can see the different output is being displayed on using style parameters within the SQL convert function. You can see that the hiredate field converts the date field into different styles.

Results from using the SQL convert function

Example 6: The differences and similarities between CAST and CONVERT

In the following example, you can see that the use of CAST and CONVERT in the following T-SQL to compare the execution times.

Let us run the following T-SQL to use SQL CAST function

Differences and similarities between SQL CONVERT and CAST

Now, run the same T-SQL with SQL CONVERT function in place.

Quick tips:

  • CAST is purely an ANSI-SQL Standard. But, CONVERT is SQL Server specific function likewise we’ve to_char or to_date in Oracle
  • CAST is predominantly available in all database products because of its portability and user-friendliness
  • There won’t be a major difference in terms of query execution between SQL Cast and SQL Convert functions. You can see a slight difference in execution times this is because of internal conversion of SQL CAST to its native SQL CONVERT function but CONVERT function comes with an option “Style-code” to derive various combinations of date-and-time, decimals, and monetary values. In any case, SQL CONVERT function runs slightly better than the SQL CAST function

Example 7: Style code in the calculation using CONVERT

In this example, you’ll list rate field from “HumanResources.EmployeeHistory” table of Adventureworks2016 database. The second column in the SQL uses the SQL CONVERT function with the data type char(10) and the expression rate data multiply it by 1000.

The first SQL, the style code is not used but for the second and third SQL, the style code is set to 1.

In the output, we can see that the query with the style code of 1 returns a text output with a comma separator at the thousands position. The query with the style code 0 results in the text output with no comma separator.

Style code in the calculation using SQL CONVERT

Wrap Up

So far, we talked about the SQL cast, SQL try_cast and SQL convert functions and how you can convert between strings, integers, and the date-and-time values. We also looked into the SQL CONVERT function with the style-code. We walked through some examples of the implicit and explicit SQL conversion functions. And we understood the difference between SQL CAST and SQL CONVERT functions. You also see a conditional expression is used with SQL ISNULL along with the SQL try_cast, and again, if I had done that without using the try part of that, it would have failed instead of returning a value. This is how you can cast values, which is pretty standard, with this additional level, and also and errors more gracefully.

That’s all for now… Hope you enjoyed reading this article. Feel free to comment below.


Prashanth Jayaram
168 Views