The SQL Server CONVERT function is an extremely handy tool to know when it comes to querying SQL Server databases. CONVERT is a system function we can use to very easily convert a value from one data type to another on-the-fly.
In this very brief tutorial, you’ll learn everything you need to know about the CONVERT function. We’ll go through several examples of it’s use and discuss some helpful tips you should know.
We’ll discuss these topics:
- What is the CONVERT function?
- The syntax of the CONVERT function.
- Examples of using CONVERT
- The difference between CAST and CONVERT
- Tips, tricks, and links.
By the way, in order to understand the CONVERT function, you should really understand a-thing-or-two about data types. Are you well versed on your SQL Server data types? If not, you should download this FREE guide:
FREE EBook on SQL Server Data Types!
Without further ado, let’s get into it:
1. What is the CONVERT function?
CONVERT is a T-SQL system function used to convert a column or expression from one data type to another “on the fly”. As part of it’s syntax, you specify a style to use for the conversion, making it a very safe conversion function.
As mentioned, CONVERT is a T-SQL specific tool, meaning it was invented by Microsoft and it can only be used in a Microsoft SQL Server database.
That’s what T-SQL is, by the way. It’s Microsoft’s flavor of the standard SQL language. T-SQL has several tools it offers that make up for some of the shortcomings of the standard SQL language (as we will soon learn).
2. The syntax of the CONVERT function
The syntax for CONVERT is very simple. It follows this pattern:
CONVERT(<target data type>, <column or expression>, [<optional style number>])
Let’s discuss each part of this syntax:
- Your first argument is the target data type you want your column or expression to be converted to.
- The second argument is your column or expression that you want to convert “on the fly”
- The final, optional argument is the style you want to use for the conversion. It’s simply a number. We’ll see what numbers we can choose from soon.
All these arguments are separated by a comma, and they are all within parentheses.
We need to look at some examples (especially regarding the style argument. WTF is that?)
3. Examples of using CONVERT
The best way to understand the SQL Server CONVERT function is to look at some examples. First, let’s use CONVERT to convert a simple integer into a DECIMAL (without using the optional style argument):
The result is 100.00. The decimal places are a clue that our number is now a DECIMAL data type, but we can use the handy SQL_VARIANT_PROPERTY system function to prove it:
Nice.
How to convert an integer into a string:
One common task you might need to perform is converting an integer into a string. It’s very simple to do with the CONVERT function:
And once again, we’ll use the SQL_VARIANT_PROPERTY to prove that the resulting value is a VARCHAR data type:
Nice. By the way, I could have used the regular CHAR data type in my CONVERT function, too. It’s another string data type. Learn more about all the string data types we have available to us in SQL Server here:
SQL Server character data types (and the differences between them)
Using the optional “style” argument in the CONVERT function
Ok, we’ve seen plenty of examples of using CONVERT without the style argument. But let’s finally look at an example where we do use the style argument to understand why we might want to use it sometimes.
Take a look at the following date:
5/11/2022
Would you say that date is May 11, 2022? If you’re in the United States, you would say “Well, duh, of course it is“.
But hold on. If you’re in England, you would say that date is November 5, 2022. In many places in Europe, they write their dates starting with the day, then the month, then the year.
Hmm, so maybe if we want to convert the string ‘5/11/2022’ to a DATE data type, maybe we want to avoid ambiguity and make sure the resulting value is the American date of May 11, 2022.
To do that, we need to specify a style in our CONVERT function, which is simply a number. The style number for United States date and time is 101:
To prove that this date is May 11, 2022, I’ll use the handy DATEPART system function to write out the month of our date:
If I run the same statement but specify the British style value of 103 instead, we see the month of the resulting date is now November:
Again, all I changed was the style number.
Get it?
Here’s a link to the official Microsoft Documentation on the CONVERT function which outlines all the different styles from which you can choose: Cast and Convert (Transact SQL)
Using a column name or expression in the CONVERT function
This whole time, I have been showing you hard-coded literal values as the second argument in the CONVERT function. I want to show you that that’s not all you can use in the CONVERT function.
Take a look at this Books table:
Let’s say we want to take the values in the Pages column and convert them all from integers to strings. We just need to use the Pages column name in our CONVERT function:
Here’s another quick example. The GETDATE() system function returns the current date and time as a DATETIME data type, as demonstrated here (I’m writing this on February 13th 2022):
But what if we don’t really care about the time, and only want to know the actual date? We can use the CONVERT function to simply convert the returned valued from GETDATE() into a DATE data type (and we’ll specify a U.S. style while we’re at it):
Nice.
4. The difference between CAST and CONVERT
I mentioned earlier that the T-SQL language offers many tools that make up for the shortcomings of the standard SQL tools. The CONVERT function is one of those helpful T-SQL tools.
The CAST function is part of standard SQL. It can also convert values on the fly, but it does not allow you to specify a style when performing the conversion.
Let’s look at one of our previous examples and convert ‘5/11/2022’ to a DATE data type using CAST:
You can see that the syntax is a bit different from CONVERT. But the most important thing to understand is that there is no way to specify a style when using CAST. The resulting value in our example is May 11, 2022 as proven by using DATEPART again:
But what if you wanted this value to be converted using the British format? You’re out of luck. I suppose you would need to change your input value to ’11/5/2022′, which I suppose is easy enough if we’re using a string literal. But what if you are using an actual column name or some other type of expression that you can’t easily change?
Answer: Use CONVERT instead!
5. Tips, tricks, and links
There is only one trick I’d like you to know about the SQL Server CONVERT system function.
Tip # 1: There is a handy TRY_CONVERT variation that you can use.
What do you think will happen if we try to run the following statement?:
SELECT CONVERT(INT, 'Spagetti') AS 'Spagetti INT?'
Nothing good:
Obviously, you can’t convert the word ‘Spagetti‘ to an integer. There is a wonderful variation on the CONVERT function called TRY_CONVERT that can help us. This function will try to convert your input value to the target data type, but if it fails, you won’t get an error message. Instead, it will return NULL:
It’s always better to not generate an error message if possible.
We can use TRY_CONVERT as an error-handling tool and basically check if our conversion was successful. If the conversion was successful, the converted value is returned. If the conversion was unsuccessful, NULL is returned. This would be great to use in an ISNULL check, for example:
Since TRY_CONVERT returned NULL, we return the specified default value of 0 instead.
Superb!
Links
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 CONVERT and CAST functions, as well at the “TRY_” variations of each: Conversion Functions (Transact-SQL)
Next Steps:
Leave a comment if you found this tutorial helpful!
As mentioned earlier, you need to know a-thing-or-two about data types to truly understand the CONVERT function. You should definitely download my FREE guide on the most common data types you should know as a starting point. Get it here:
FREE EBook on SQL Server Data Types!
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!
