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:
|
1 2 3 4 |
CAST CAST ( exp AS datatype [ ( len ) ] ) CONVERT CONVERT ( datatype [ ( len ) ] , expression [ , style ] ) |
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
Example 1: SQL Implicit conversion with numbers
In the following example, two random numbers 5 and 212345 are added and results are displayed.
|
1 2 |
DECLARE @a int=5, @b bigint=212345 SELECT @a, @b, @a+@b |
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’.
|
1 2 |
DECLARE @a char='5', @b char='2' SELECT @a, @b, @a+@b |
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.
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.
|
1 2 |
DECLARE @a char='5', @b char='2' SELECT @a,@b, cast(@a as int)+ cast(@b as int) |
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.
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.
|
1 2 3 4 5 6 7 |
SELECT TRY_CAST('123' AS BIGINT); GO SELECT CAST('xyz' AS BIGINT); GO SELECT TRY_CAST('xyz' AS BIGINT); GO SELECT ISNULL(TRY_CAST('xyz' AS BIGINT), 99); |
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.
|
1 2 3 4 5 6 7 8 9 |
SELECT hiredate, CONVERT(CHAR(50), hiredate), CONVERT(CHAR(50), hiredate, 1) 'S-1', CONVERT(CHAR(50), hiredate, 101) 's-101', CONVERT(CHAR(50), hiredate, 102) 's-102', CONVERT(CHAR(50), hiredate, 103) 's-103', CONVERT(CHAR(50), hiredate, 104) 's-104', CONVERT(CHAR(50), hiredate, 105) 's-105' FROM HumanResources.Employee; |
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.
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
|
1 2 3 4 5 6 7 8 9 10 11 |
SET STATISTICS TIME ON WITH temp AS (SELECT CAST(GETDATE() AS DATE) AS cast_date, 0 AS n UNION ALL SELECT CAST(GETDATE() + n AS DATE) AS cast_date, n + 1 FROM temp WHERE n < 1000000) SELECT MAX(cast_date) FROM temp OPTION(MAXRECURSION 0); |
Now, run the same T-SQL with SQL CONVERT function in place.
|
1 2 3 4 5 6 7 8 9 10 11 |
SET STATISTICS TIME ON WITH temp AS (SELECT CONVERT(DATE, GETDATE(), 20) AS convert_date, 0 AS n UNION ALL SELECT CONVERT(DATE, GETDATE() + n, 20) AS convert_date, n + 1 FROM temp WHERE n < 1000000) SELECT MAX(convert_date) FROM temp OPTION(MAXRECURSION 0); |
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.
|
1 2 3 4 5 6 7 8 9 |
SELECT TOP 3 Rate, CONVERT(CHAR(10), rate * 1000) AS TextPrice FROM HumanResources.EmployeePayHistory; SELECT TOP 3 Rate, CONVERT(CHAR(10), rate * 1000, 0) AS TextPrice FROM HumanResources.EmployeePayHistory; SELECT TOP 3 Rate, CONVERT(CHAR(10), rate * 1000, 1) AS TextPrice FROM HumanResources.EmployeePayHistory; |
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.
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.
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021