MySQL Convert() Function
The CONVERT() function in MySQL is used to convert a value from one data type to the other data type specified in the expression. MySQL also allows it to convert a specified value from one character set to the different character set.
The following are the data types on which this function works perfectly:
Datatype | Descriptions |
---|---|
DATE | It converts the value into DATE datatype that responsible for the date portion only. It always results in the “YYYY-MM-DD” format. It supports the range of DATE in ‘1000-01-01’ to ‘9999-12-31’. |
DATETIME | It converts the value into the DATETIME data type that responsible for the date and time portion both. It always results in the “YYYY-MM-DD HH:MM:SS” format. It support the range in ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’. |
TIME | It converts the value into a TIME data type that responsible for the time portion only. It always results in the “HH:MM:SS” format. It supports the range of time in ‘-838:59:59’ to ‘838:59:59’. |
CHAR | It converts a value to the CHAR data type, which has a fixed-length string. |
SIGNED | It converts a value to SIGNED datatype, which has signed 64-bit integer. |
UNSIGNED | It converts a value to the UNSIGNED datatype, which has unsigned 64-bit integer. |
DECIMAL | It converts a value to the DECIMAL data type, which has a decimal string. |
BINARY | It converts a value to the BINARY data type, which has a binary string. |
Syntax
The following are the syntax of CONVERT() function in MySQL:
Parameter Explanation
This syntax accepts the following parameters, which are going to be discussed below:
Parameter | Requirement | Descriptions |
---|---|---|
expression | Required | It is a specified value going to be converted into another specific datatype. |
datatype | Required | It specifies the desired data type in which we want to be converted. |
character_set | Required | It specifies the desired character set in which we want to be converted. |
Return Value
It will return a value in which data type or character set we want to convert.
MySQL version support
This function can support the following versions:
- MySQL 8.0
- MySQL 5.7
- MySQL 5.6
- MySQL 5.5
- MySQL 5.1
- MySQL 5.0
- MySQL 4.1
- MySQL 4.0
Let us understand the MySQL CONVERT() function with the following examples. We can use the CONVERT function with the SELECT statement directly.
Example 1
When we implement the statement, it will convert the value into DATETIME datatype.
Output
Example 2
When we implement the statement, it will convert the value into UNSIGNED datatype.
Output
Example 3
When we implement the statement, it will convert the value into SIGNED datatype.
Output
Example 4
When we implement the statement, it will convert the string value into an utf8mb4 character set.
Output
Example 5
Sometimes there is a need to convert a string between different character sets. In that case, we use the following statement for conversion:
Output
Example 6
The following statement first converts an integer value into string datatype and then perform concatenation with another specified string.
Output
Example 7
In this example, we are going to see how the CONVERT function works with the table. Let us first create a table “Orders” that has the following data:
In the above table, the Order_Date is in DATE datatype. So if we want to get a product name between selected ranges of time, execute the statement below.
We will get the following output:
Difference between CAST AND CONVERT Function
The main difference between CAST function and CONVERT function summarized in the below table:
SN | CAST() Function | CONVERT() Function |
---|---|---|
1. | We use it to convert one data type into the other data type. | We use it to convert one data type into the other data type. |
2. | It cannot allow us to convert a character set. | It can be used to convert the character set. |
3. | It is the part of ANSI-SQL specification. | It is not the part of ANSI-SQL specification. |
4. | It uses “AS” for separating the parameter. | It uses “comma(,)” or “USING” for separating the parameter. |