Javatpoint Logo
Javatpoint Logo

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

MySQL Convert() Function

Example 2

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

Output

MySQL Convert() Function

Example 3

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

Output

MySQL Convert() Function

Example 4

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

Output

MySQL Convert() Function

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

MySQL Convert() Function

Example 6

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

Output

MySQL Convert() Function

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:

MySQL Convert() Function

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:

MySQL Convert() Function

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.

Next TopicMySQL COALESCE()




Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA