intro
Explore the CONVERT function offered by some database management systems and become an SQL data conversion wizard.
Converting data from one type to another is one of the most common tasks when dealing with databases. That is because having values of the same type simplifies data conversion and allows you to call specific utility functions. Here is why SQL comes with built-in data type conversion functions! Enter SQL CONVERT, one of the most popular non-standard functions to convert data in database systems like SQL Server and MySQL.
In this article, you will learn what the CONVERT SQL function is, how it works in MySQL and SQL Server, and how to use it in real-world examples.
Let's dive in!
What Is the SQL CONVERT Function?
In SQL, CONVERT is an SQL function to convert a value from one data type to another. Since it is not a standard SQL function, the implementation of CONVERT may vary between different database systems. In detail, SQL Server and MySQL are the only major DBMS technologies that support it.
Oracle also has a CONVERT function, but it can only convert a character string from one character set to another. So, it is not as general purpose as the SQL CONVERT function offered by SQL Server and MySQL.
CONVERT SQL FUNCTION: Syntax and Examples
As mentioned earlier, there is no standard version of the CONVERT SQL function. This means that different database systems may implement it differently or not even have it at all.
Here, you will see how to use the function in SQL and MySQL.
SQL Server
This is the syntax of the SQL Server CONVERT function:
1
CONVERT(data_type, expression [, style])
The parameters involved in the above SQL instruction are:
This function can be used to convert a float to an integer, as shown below:
1
SELECT CONVERT(int, 42.35);
The result will be:
1
42
The decimal part of the number has been cut off as expected.
Keep in mind that it is not always easy to tell if data has been converted as expected (i.e., when converting an integer to a string or vice versa). In DbVisualizer, the database client with the highest user satisfaction on the market, if you select a cell, it gives you its type in the bottom bar:
1
CONVERT(expression, data_type)
Where the arguments are:
See the function in action in the example below:
1
SELECT CONVERT(42, CHAR);
That will produce this string:
1
42
Verify that it is a string and no longer a number by clicking on it:
Instead, when converting a string to a different character set, the syntax is:
1
CONVERT(string_expression USING charset)
To use it, you now have to write something like:
1
SELECT CONVERT('Pelé' USING ASCII);
As ASCII does not involve accented characters, the result will be:
1
Pel?
Note that the SQL Server CONVERT function and the MySQL CONVERT function have opposite syntax in terms of parameter ordering.
Popular SQL Data Conversion Scenarios
Now that you know what the SQL CONVERT function is and how to use it, it is time to see it in action in some real-world scenarios. For the sake of simplicity, the following examples will be in MySQL, but you can easily adapt them to SQL Server.
SQL CONVERT: String to Date
In this scenario, you have a date stored as a string and you need to convert it to the DATE data type. For example, for proper date manipulation and comparison. Here is how you can do it:
1
SELECT CONVERT('2024-01-18', DATE);
The resulting 2024-01-18 data will be a MySQL DATE.
SQL CONVERT: Date to String
In this case, you want to display a date as a string. To achieve that, use the SQL CONVERT function below:
1
SELECT CONVERT(CURDATE(), CHAR);
The result will be:
1
2024-01-18
Note the use of CURDATE() instead of the more popular NOW(). We use NOW() because it also returns the time information, while we only want a date to be returned.
SQL CONVERT: Int to String
When dealing with integer values that need to be presented as strings, use the MySQL CONVERT function as in this example:
1
SELECT CONVERT(27, CHAR);
That will produce a string containing the value:
1
27
SQL CONVERT: String to Int
This time, you have a string and you want its integer representation. Get that with:
1
SELECT CONVERT('56', SIGNED);
The result? The number:
1
56
SQL CONVERT: Float to Int
Converting a float to an integer in MySQL may be a bit more tricky as there are a few rules to consider. In detail, if the expression argument is of type DECIMAL, DOUBLE, DECIMAL, or REAL, the MySQL CONVERT function will round the value to the nearest integer by default.
Thus, this example will produce 48:
1
SELECT CONVERT(47.5, SIGNED);
SQL CONVERT: Datetime to Date
What if you want to remove the time information from a DATETIME value? You can do it as below:
1
SELECT CONVERT(NOW(), DATE);
Naturally, NOW() will return something like 2024-01-18 10:13:52, but this time the result will be:
1
2024-01-18
In MySQL, the above query is equivalent to:
1
SELECT DATE(NOW());
Best Practices
Here is a list of some SQL CONVERT best practices:
Conclusion
In this guide, you understood what the CONVERT function SQL is and how it works. You now know that CONVERT is a powerful data type conversion function available in SQL Server and MySQL. Thanks to the use cases shown above, you have also learned how and when to use it in real-world scenarios.
Figuring out the type of a value is not always easy, but with a powerful database client like DbVisualizer, it becomes a piece of cake! With a single click on a cell, you can get instant feedback on the data type of the value stored in it. This is just one of dozens of features supported by this powerful tool, which also includes advanced query optimization capabilities and automatic ERD-like schema generation. Try DbVisualizer for free today!
FAQ
Is the SQL CONVERT function part of standard SQL?
No, the SQL CONVERT function is not part of the standard SQL language. It is specific to certain database management systems, such as Microsoft SQL Server, MySQL, Sybase, and (partially) Oracle.
Can any SQL data type be converted to another, or are there any restrictions?
Many SQL data types can be converted to others using CONVERT, but there are some restrictions. Compatibility depends on the specific database system, and some conversions may result in loss of accuracy or data truncation. That is why SQL Server offers a complete data type conversion table.
What is the difference between SQL CONVERT and SQL CAST functions?
Both SQL CONVERT and CAST functions can be used for data type conversion, but they differ in syntax and usage. CAST is standard SQL and is generally supported across database systems, while CONVERT is specific to certain database technologies. Always refer to the documentation of the specific database system for precise details on their implementation.
Is there some CONVERT function SQL more specific alternatives?
In addition to general-purpose conversion functions like CAST, some database systems also offer more specific functions for certain conversions. For instance, the MySQL function DATE_FORMAT is more specific for formatting date and time values. These specialized functions cater to particular data type transformations, providing more granular control over the conversion process.
Do SQL Server and MySQL behave the same when converting float to integer?
No, SQL Server and MySQL do not behave the same when converting from float to integer. MySQL uses rounding behavior, rounding towards the next integer, while SQL Server truncates the decimal part without rounding. Therefore, the results of such conversions will differ between the two database systems.
