MySQL CAST() function explained

The MySQL CAST() function allows you to convert the expression value of one type into another type.

For example, you can change a CHAR type value into a DATETIME value as follows:

SELECT CAST('2020-10-02' AS DATETIME);

The SQL query above will produce the following result set:

+--------------------------------+
| CAST('2020-10-02' AS DATETIME) |
+--------------------------------+
| 2020-10-02 00:00:00            |
+--------------------------------+

The syntax for CAST() function is CAST( [expression] AS [type] ).

You can put a literal value or a column name as the argument for the expression parameter.

The type parameter must be of a valid MySQL data type. It can be one of the following:

• CHAR converts the expression into a VARCHAR type
• BINARY converts the expression into a VARBINARY type
• DATETIME converts the expression into a DATETIME type with the YYYY-MM-DD HH:MM:SS format
• DATE converts the expression into a DATE type with the YYYY-MM-DD format
• TIME converts the expression into a TIME type with the HH:MM:SS format
• YEAR converts the expression into a YEAR type. Return year value between 1901-2155 inclusive
• SIGNED converts the expression into SIGNED a positive-negative integer
• UNSIGNED converts the expression into UNSIGNED a positive integer from 0 to 4294967295
• DECIMAL, DOUBLE, FLOAT converts the expression into a floating number with various precisions
• JSON converts the expression into JSON type

The CAST() function is particularly useful to filter your query and produce a more specific result set.

For example, imagine you have the following members table in your MySQL database:

+----+------------+------------+
| id | first_name | join_date  |
+----+------------+------------+
|  1 | Jackson    | 2021-09-22 |
|  2 | Sarah      | 2021-09-23 |
|  3 | Akihiko    | 2021-09-24 |
|  4 | Matt       | 2021-09-25 |
+----+------------+------------+

Suppose you want to SELECT the particular rows that have the join_date value between 23rd and 24th September.

You can use the CAST() function in the BETWEEN clause as follows:

SELECT id, first_name, join_date 
  FROM members 
  WHERE join_date 
  BETWEEN CAST('2021-09-23' AS DATE) 
    AND CAST('2021-09-24' AS DATE);

The query above will produce the following result:

+----+------------+------------+
| id | first_name | join_date  |
+----+------------+------------+
|  2 | Sarah      | 2021-09-23 |
|  3 | Akihiko    | 2021-09-24 |
+----+------------+------------+

Casting from VARCHAR to INT

As you’ve seen in the list above, the INT type is not considered a valid type in MySQL, so you can’t convert the VARCHAR type to INT type using the following queries:

SELECT CAST('200' AS INT); -- Error
SELECT CAST('200' AS INTEGER); -- Error

Both queries above will cause MySQL to throw an error.

To convert your expression into INT type, you need to use either SIGNED or UNSIGNED integer type as shown below:

SELECT CAST('200' AS SIGNED), CAST('200' AS UNSIGNED);

The query above should work fine:

+-----------------------+-------------------------+
| CAST('200' AS SIGNED) | CAST('200' AS UNSIGNED) |
+-----------------------+-------------------------+
|                   200 |                     200 |
+-----------------------+-------------------------+

Finally, some conversion such as YEAR or JSON has some rules about the conversion that you need to pay attention to.

You can find the rules in the official MySQL documentation.

And that’s how the MySQL CAST() function works. 😉