Two of the most common system functions you will see as a database developer are the CAST and CONVERT functions. These two functions perform in a similar way, with only slight differences between the two. The one you use will depend on your needs at the time.
CAST and CONVERT are part of the Top 6 tools you should know for writing AWESOME Select statements. If you want to learn more about other extremely common and useful query tools, check out that tutorial.
Do you need to know the difference between these two conversion functions? Are you not sure how the syntax is supposed to be? We’ll answer these questions and more in this tutorial!
We will discuss the following topics:
- The CAST conversion function
- An example of using CAST
- The CONVERT conversion function
- An example of using CONVERT
- The “TRY_” variations on CAST and CONVERT
- Tips, Tricks, and Links
To understand CAST and CONVERT, you should really understand a thing or two about data types. I created a FREE Ebook on the most common data types in SQL Server you should know. Make sure you check it out:
FREE Ebook on SQL Server Data Types!
Let’s take it from the top:
1. The CAST conversion function
The CAST conversion function is used when you simply need to convert an expression or column from one data type to another on the fly when writing a query.
CAST is a “standard” function. The word “standard” refers the standard SQL, which is recognized by the International Organization for Standardization (ISO) and the American National Standards Institute (ANSI).
(I know, “get to the point“)
The idea is that all the different database management vendors agree to make their system work with an agreed-upon set of tools and rules.
To bring it back to our topic, the CAST system function will work the same regardless of what database management system it is ran on. You can run it on Microsoft SQL Server, Oracle, PostgreSQL, etc. and it will perform the same!
They say when given the choice, you should choose to use standard tools over vendor-specific tools. That’s because if the database ever needs to be moved to a different database management system, things won’t break.
Spoiler Alert: The CONVERT function is a Microsoft-specific tool used exclusively in the T-SQL flavor of SQL. More on that when we discuss CONVERT.
2. An example of using CAST
Let’s look at the syntax of the CAST function:
CAST(<column or expression>) AS <target data type>)
It’s pretty straightforward. If you have a column or expression that you need converted on the fly, you specify it as the first argument to the CAST function.
Then you use the keyword ‘AS‘, followed by the data type you need your column or expression converted to.
It reads very nicely, almost like you would read it in a sentence.
A simple example of CAST
Let’s look at a very simple example of CAST. Let’s say we want to convert the number 7 to a decimal. It’s normally an integer, but we want to turn it into a decimal.
Let’s say we want the decimal to have a precision of 4 and a scale of 2. In other words, a data type of DECIMAL(4,2).
Learn more about the DECIMAL data type: SQL Server DECIMAL: Everything you need to know
To perform the conversion using CAST, the statement would look like this:
Pretty simple, right? If we wanted to add more decimal places, we can do that. Let’s change the precision to 8, and the scale to 6:
Nice, now we have 6 digits after the decimal!
We can also do an opposite conversion, where we convert a decimal to an integer.
Check it out:
And no, the CAST operation didn’t just “round it down”. When you convert a decimal to an integer, the numbers after the decimal are truncated. If we used CAST to convert 4.999999 to an INT, we still would have gotten 4.
Don’t believe me? Shame on you:
A real world example of CAST
Let’s think about a better real-world example of when we might want to use the CAST function.
Imagine you are the owner of a small woodworking business. You use a SQL table to keep track of the products in your inventory.
Here is the data in that Products table:
Say you needed to know the average price for your products. You can use a simple aggregate query to get that information:
Cool, but what if you wanted to see this number rounded to the nearest whole cent? In other words, you only want to see 2 places after the decimal instead of the 6 it gives you by default.
We can use CAST to achieve that:
Perfect!
3. The CONVERT conversion function
The CONVERT conversion function is used when you need to convert an expression or column from one data type to another on the fly, and you need to specify a “style” to avoid ambiguity in the resulting value.
CONVERT is a T-SQL specific conversion function. This means it’s NOT “standard”.
T-SQL is Microsoft’s flavor of the SQL language. Microsoft recognized there were some shortcomings in the standard SQL tools, so they invented their own language called T-SQL which is based on standard SQL, of course, but it has some pretty slick enhancements to compensate for standard SQL’s shortcomings.
We’ll talk about why CONVERT is one of those “slick” tools soon.
Spoiler Alert: It’s because of the “style” you can use when converting.
4. An example of using CONVERT
First, let’s go over the syntax of CONVERT. It’s not as intuitive as CAST :
CONVERT(<target data type>, <column or expression>, [<optional style number>])
So your first argument is the data type you want your column or expression to be converted to.
Then you outline your column or expression, then the style you want to use if you need to.
(I’m going to tell you what the “style” means in a moment)
All these arguments are separated by a comma.
A simple example of CONVERT
Let’s look at another very simple example. In fact, let’s use the same examples we used before, where we want to convert an integer to a decimal.
We’ll use CONVERT to convert the number 7 to a decimal (and leave off the optional “style” part for now):
And we’ll use CONVERT to convert the decimal 4.999999 to an integer (and leave off the optional “style” part again):
So far, CONVERT seems to work just like CAST, right? Again, if it’s all the same, you should choose to use the CAST function because it’s standard.
But the “style” is where things get weird.
A real world example of using CONVERT
Ok, so I’ve been teasing this “style” thing for long enough. Let’s get into it.
What is the month and day of this date?:
5/7/2021
“Why, duh, it’s May 7th“.
Are you sure about that? What do you think our British brothers and sisters would say? They write their dates different from people in the United States.
In Britain, they write the day first, followed by the month, followed by the year. They would look at that date and say “Why, duh, it’s July 5th“.
Who’s right?
That’s the reason why sometimes we need to specify a style when we are converting data.
Let’s say we want to convert the string ‘5/7/2021’ to a DATETIME data type, and we want to make sure it’s in U.S. format. We would write the following query:
The style is just a numeric code. In our example, we need to use a style of 101 to represent U.S. format for our date. The full list of styles can be seen in the official Microsoft documentation: Cast and Convert (Transact SQL)
To further prove this converted date is in fact May 7th, let’s use our handy DATENAME system function:
Now, let’s run the same query again, but this time make it in British format:
Well I’ll be darned.
5. The “TRY_” variations of CAST and CONVERT
There is a problem with CAST and CONVERT you need to be aware of.
What do you think will happen if we try to do the following conversion:
SELECT CAST('Spagetti' AS INT)
Well, ‘Spagetti’ is certainly not a number, so this conversion will likely FAIL. Let’s try it:
When a conversion fails, it fails big. The entire query blows up. This can obviously be an undesirable effect if you have a particularly long and complicated query.
What if instead of failing, we just return NULL? That way, the entire query doesn’t blow up.
It’s possible with the “TRY_” variations of CAST and CONVERT. The idea is that we try the conversion. If it succeeds, we return the converted value. Otherwise, we return NULL.
The syntax is extremely simple. Just put “TRY_” in front of the word CAST or CONVERT (so the whole thing would be TRY_CAST or TRY_CONVERT).
Let’s use TRY_CAST in our example and see what happens:
Much better!
It’s probably good to get in the habit of using the “TRY_” variations of CAST and CONVERT. We would prefer it if our queries didn’t blow up if we attempt an invalid conversion.
6. Tips, Tricks, and Links
Here is a list of a few tips and tricks you should know about the CAST and CONVERT functions:
Tip # 1: There is such a thing as “implicit conversions“
Implicit conversions are data type conversions that occur automatically without your input. It happens all the time.
Take a good look at this query:
SQL Server is smart enough to recognize that our value stored in @bigNum is just the character-string form of a number. In this case, SQL Server can implicitly convert that string to an integer to do the simple math equation.
If we try to put ‘Spaghetti’ in there again, SQL Server throws it’s hands up and says “What on earth are you doing?“:
Sometimes it’s okay to rely on the implicit conversion process, but sometimes it isn’t. It’s your job to make sure explicit conversions are done when necessary using either CAST or CONVERT.
Tip # 2: TRY_CAST and TRY_CONVERT were introduced in SQL Server 2012.
If you’re trying to use them in an older version, they won’t work!
Links
There is a great book called T-SQL Fundamentals written by Itzik Ben-Gan that goes over several core concepts you should know about SQL Server, including the CAST and CONVERT functions. This book helped me understand several important tools and concepts within Microsoft SQL Server. You definitely won’t regret owning this book, trust me. Get it today!
Also, here is a link to the official Microsoft documentation on the topic of conversion functions. On this page, you will find links to both the CAST and CONVERT functions, as well at the “TRY_” variations of each: Conversion Functions (Transact-SQL)
Next Steps:
Leave a comment if you found this tutorial helpful!
To understand data type conversions, you need to understand the different data types first! I put together a great guide that goes over the most common data types you will see and use when working with SQL. Make sure you check it out:
FREE 1-page guide on the Top 10 Data Types you need to know!
As mentioned earlier, CAST and CONVERT are part of the Top 6 tools you should know for writing AWESOME Select statements. If you want to learn more about other extremely common and useful query tools, check out that tutorial.
Also, a common place you will likely need to write complicated queries that do data conversion is in stored procedures. Make sure you are familiar with them and how they can help you be a great SQL developer:
Stored Procedures: The Ultimate Guide for Beginners
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, please leave a comment. Or better yet, send me an email!
Thank you very much! it helped me to understand both functions 😀
You’re welcome! Glad I could help 🙂