MySQL CAST() Function

If you are interested to learn about the MySQL Decimal

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 Cast() function provides a data type to a dynamic parameter (?) or a NULL value.

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:

CAST(expression AS datatype);  

How do you use CAST function?

We use the CAST function to convert numeric data into character or string data.
SQL CAST Function

1. CAST (EXPRESSION AS Data_ Type[(Length)]
2. _ _ CAST in the SQL example.
3. SELECT CAST (123 AS VARCHAR (20)) [result_name]
4. FROM [Source]

How CAST function works in SQL?

In SQL Server (Transact-SQL), the CAST function converts an expression from one datatype to another datatype. If the conversion fails, the function will return an error. Otherwise, it will return the converted value. TIP: Use the TRY_CAST function to return a NULL (instead of an error) if the conversion fails.

Parameter Explanation

This syntax accepts two parameters, which are going to be discussed below:42.7M805Difference between JDK, JRE, and JVMNextStay

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.

SELECT CAST("2018-11-30" AS DATE);  

Output

Example 2

This statement converts the value into SIGNED datatype.

SELECT CAST(3-6 AS SIGNED);  

Output

Example 3

This statement converts the value into UNSIGNED datatype.

SELECT CAST(3-6 AS UNSIGNED);  

Output

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.

SELECT (3 + CAST('3' AS SIGNED))/2;  

Output

Example 5

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

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

Output

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:

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.

SELECT Order_ID, Product_Name FROM Orders   
WHERE Order_Date   
BETWEEN CAST('2020-02-01' AS DATETIME) AND CAST('2020-02-28' AS DATETIME);  

This statement will produce the following output:

MySQL CAST function examples

In the following example, MySQL converts a string into an integer implicitly before doing calculation:

<code>SELECT (1 + '1')/2;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

Output:

To explicitly convert a string into an integer, you use the CAST() function as the following statement:

<code>SELECT (1 + CAST('1' AS UNSIGNED))/2;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

Output:

The following statement explicitly converts an integer into a string and concatenate the string with another string:

<code>SELECT CONCAT('MySQL CAST example #',CAST(2 AS CHAR));</code><small>Code language: SQL (Structured Query Language) (sql)</small>

Output:

Let’s take a look at the orders table

See the following query:

<code>SELECT orderNumber,
       requiredDate
FROM orders
WHERE requiredDate BETWEEN '2003-01-01' AND '2003-01-31';</code><small>Code language: SQL (Structured Query Language) (sql)</small>

Output;

The query selects orders whose required dates are in January 2003. The data type of the requireDate column is DATE, therefore, MySQL has to convert the literal strings: '2003-01-01' and '2003-01-31' into values before evaluating the WHERE condition. However, to be safe, you can use CAST() function to explicitly convert a string into a TIMESTAMP value as follows:

<code>SELECT orderNumber,
       requiredDate
FROM orders
WHERE requiredDate BETWEEN  CAST('2003-01-01' AS DATETIME)
                        AND CAST('2003-01-31' AS DATETIME);</code><small>Code language: SQL (Structured Query Language) (sql)</small>

Output:

The following statement converts DOUBLE values into CHAR values and uses the results as the arguments of the function:

<code>SELECT productName,
       CONCAT('Prices(',
               CAST(buyprice AS CHAR),
               ',',
                CAST(msrp AS CHAR),
      ')') prices
FROM products;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

Output:

In this tutorial, you have learned how to use the MySQL CAST() function to convert a value with any type into a value with a specified type.