MySQL Convert() Function

If you are interested to know about the MySQL CAST() 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.

CONVERT(expression, datatype);  
OR,  
CONVERT(expression USING character_set);  

Does convert work in MySQL?

The MySQL CONVERT() function is also used for converting a value from one character set to another character set. It accepts two parameters which are the input value and the type to be converted in. The CONVERT() function returns the value in the specified datatype or character set

Parameter Explanation

This syntax accepts the following parameters, which are going to be discussed below:Skip Ad

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.

SELECT CONVERT("2018-11-30", DATETIME);

Output

Example 2

When we implement the statement, it will convert the value into UNSIGNED datatype.

SELECT CONVERT(4-7, UNSIGNED);

Output

Example 3

When we implement the statement, it will convert the value into SIGNED datatype.

SELECT CONVERT(CONVERT(4-7, UNSIGNED), SIGNED);

Output

Example 4

When we implement the statement, it will convert the string value into an utf8mb4 character set.

SELECT CONVERT('javatpoint' USING utf8mb4);

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:

SELECT CONVERT('javatpoint', CHAR CHARACTER SET utf8mb4);

Output:

Example 6

The following statement first converts an integer value into string datatype and then perform concatenation with another specified string.

SELECT CONCAT('CONVERT Function Example ## ',CONVERT(5, CHAR));

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.

SELECT Order_ID, Product_Name FROM orders   
WHERE Order_Date   
BETWEEN CONVERT('2020-02-01', DATETIME) AND CONVERT('2020-03-10', DATETIME);

We will get the following output:

Difference between CAST AND CONVERT Function

The main difference between CAST functionand 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.

More Examples Of MySQL CONVERT() Function

Though the CONVERT() function allows you to convert the value into a specified data type, the number of data types is limited to convert the value into. You can convert the data into one of the following data types: DATE, DATETIME, TIME, DECIMAL, CHAR, BINARY, DOUBLE, FLOAT, SIGNED, UNSIGNED, YEAR.

We will see a few data types conversion here, which will help you understand the usage of the CONVERT() function. You can check MySQL official documentation for the implementation of every data type. Let’s start with the DECIMAL data type. As the name suggests, DECIMAL produces a decimal value and has two optional parameters; M and D, where M is the maximum number of digits, also known as precision, and D is a number of digits after decimal point known as scale.

The basic syntax for it is:

CONVERT(exp, DECIMAL[(M,[,D])])

The square bracket describes- everything inside the brackets is optional. Here are some examples of DECIMAL.

SELECT CONVERT(20.23, DECIMAL(3,1));

As you can see, the output contains three digits in total and a single digit after the decimal point as specified in the parameters to the decimal data type. You can also choose not to pass any parameter to the DECIMAL data type, which will eventually give you output as an integer value. Check the example below.

SELECT CONVERT(20.23, DECIMAL);

Note, if you are not passing any parameter to the data type, you must write the name without parenthesis, or you will get an error. Now, We will check the functionality of the CONVERT() function with date and time values so that all your doubts will be clear. Let’s convert the string date-time into date-time format first. Note that we have written the standard date-time format below as a string so, the output will also be the same.

SELECT CONVERT("2021-12-15 12:20:10", DATETIME) AS DateTime;

Here, the first parameter is the string date-time value, ‘DATETIME’ is a datatype and ‘DateTime’ is an alias for the output column.

We’re receiving the exact same result as an input parameter. So, what exactly did the CONVERT() function perform in this case? To understand it, look at another example that will clear your doubt.

SELECT CONVERT("2021/12/15 12:20:10", DATETIME) AS DateTime;

You can see, we have written the date-time parameter in a different style, but the CONVERT() function casts this value into a valid DateTime format. Similarly, you can also split the day, month, and year by the dot(.), and still, you will get an output in the valid MySQL DateTime format. Now, what if you have a DateTime value inside your table, but you want only the date value to show on the output page? You can achieve this by using the CONVERT() function with DATE datatype. Check out the below example.

SELECT CONVERT("2021/12/15 12:20:10", DATE) AS Date;

You can see in the output, we have obtained the date from the parameter of DateTime format. Similarly, you can obtain only time too, using TIME datatype.

1	SELECT CONVERT("2021/12/15 12:20:10", TIME) AS Time;

That’s enough for the DATETIME data type; we will now see examples of BINARY data type in the CONVERT() function to get a clearer idea of how you can implement different data types of CONVERT() function in your query or statement.

The CONVERT() function with BINARY data type will convert a value into a binary string. It is useful when you want a strict comparison of two values or strings. Here, the strict indicates a byte by byte comparison. Didn’t understand the byte-by-byte comparison concept? Let’s see an example to understand it better.

SELECT "HELLO" = "hello";

Here, we have compared two strings. Even though both strings look similar, they are different by the letter casing. MySQL will compare two strings on a character-by-character basis, and it concludes both strings are identical; therefore, it returns 1.

SELECT CONVERT("HELLO",BINARY)= "hello";

Here, “HELLO” will be converted into a binary string, and then the byte-by-byte comparison will occur; therefore, it returns 0, unlike the previous example. We can also convert an expression into a specified character set using the USING clause with a character set name. However, you will rarely use these in your project.

SELECT CONVERT('test' USING utf8mb4);

Conclusion

This is all for MySQL CONVERT() function. We have studied and understood what CONVERT() function does, how it works, its parameters, and examples. You can try converting the value in every valid datatype using the CONVERT() function. MySQL also allows you to convert the values using the CAST() function, which you can check from here. (cast() article link) Check out the MySQL official documentation from the below link for more information.