Javatpoint Logo
Javatpoint Logo

MySQL CAST() Function

The CAST() function in MySQL is used to convert a value from one data type to another data type specified in the expression. It is mostly used with WHERE, HAVING, and JOIN clauses. This function is similar to the CONVERT() function in MySQL.

The following are the datatypes to which this function works perfectly:

Datatype Descriptions
DATE It converts the value into DATE datatype 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 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 TIME data type 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 that contains the fixed-length string.
DECIMAL It converts a value to the DECIMAL data type that contains a decimal string.
SIGNED It converts a value to SIGNED datatype that contains the signed 64-bit integer.
UNSIGNED It converts a value to the UNSIGNED datatype that contains the unsigned 64-bit integer.
BINARY It converts a value to the BINARY data type that contains the binary string.

Syntax

The following are the syntax of CAST() function in MySQL:

Parameter Explanation

This syntax accepts two parameters, which are going to be discussed below:

Parameter Requirement Descriptions
Expression Required It is a value that will be converted into another specific datatype.
Datatype Required It is a value or data type in which the expression value needs to be converted.

Return Value

After conversion, it will return a value in which data type we want to convert.

MySQL version support

The CAST function can support the following MySQL 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 CAST() function with the following examples. We can use the CAST function with the SELECT statement directly.

Example 1

This statement converts the value into DATE datatype.

Output

MySQL CAST() Function

Example 2

This statement converts the value into SIGNED datatype.

Output

MySQL CAST() Function

Example 3

This statement converts the value into UNSIGNED datatype.

Output

MySQL CAST() Function

Example 4

Sometimes there is a need to convert the string into an integer explicitly, use the following statement for converting the value into INTEGER datatype.

Output

MySQL CAST() Function

Example 5

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

Output

MySQL CAST() Function

Example 6

In this example, we are going to see how the CAST function works with the table. Let us first create a table "Orders" that contains the following data:

MySQL CAST() Function

In the above table, we can see that the Order_Date is in DATE datatype. Now, if we want to get a product name between selected ranges of time, execute the statement below. Here, the literal string converted into timestamp value before evaluating the WHERE condition.

This statement will produce the following output:

MySQL CAST() Function
Next TopicMySQL convert




Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA