How CAST() Works in MySQL

Posted on by Ian

In MySQL, CAST() is a built in function that converts a value to another data type. It takes a value of one type and returns a value of the specified type.

You provide the value as an argument when you call the function, as well as the type that you’d like it converted to.

CAST() works similar to CONVERT(), except that the syntax used is slightly different.

Syntax

The syntax goes like this:

CAST(expr AS type [ARRAY])

Where expr is the value to convert, and type is the data type that you want it converted to.

The optional ARRAY keyword can be used in MySQL 8.0.17 and higher to create a multi-valued index on a JSON array as part of CREATE INDEX, CREATE TABLE, and ALTER TABLE statement.

As of MySQL 8.0.22, we have the option of using the following syntax:

CAST(timestamp_value AT TIME ZONE timezone_specifier AS DATETIME[(precision)])

Where timezone_specifier is:

[INTERVAL] '+00:00' | 'UTC'

Example

Here’s a simple example that uses the first syntax:

SELECT CAST(123.45 AS UNSIGNED INTEGER);

Result:

+----------------------------------+
| CAST(123.45 AS UNSIGNED INTEGER) |
+----------------------------------+
|                              123 |
+----------------------------------+

This cast the value to an unsigned integer, and therefore, the decimal places were removed.

Here’s another example:

SELECT CAST('2035-03-15' AS DATETIME);

Result:

+--------------------------------+
| CAST('2035-03-15' AS DATETIME) |
+--------------------------------+
| 2035-03-15 00:00:00            |
+--------------------------------+

In this case, we converted a string to a DATETIME data type.

Conversion Failure

In the previous example, we provided a valid DATE string (or DATE literal). Therefore MySQL was able to convert the value to a DATETIME data type.

However, here’s what happens when we provide an invalid DATE string:

SELECT CAST('15 Mar, 2035' AS DATETIME);

Result:

+----------------------------------+
| CAST('15 Mar, 2035' AS DATETIME) |
+----------------------------------+
| NULL                             |
+----------------------------------+

Here, MySQL apparently couldn’t figure out how to convert this value and returned NULL.

In this case, we could use another function, such as STR_TO_DATE() to perform such a conversion:

SELECT STR_TO_DATE('15 Mar, 2035', '%e %M, %Y');

Result:

+------------------------------------------+
| STR_TO_DATE('15 Mar, 2035', '%e %M, %Y') |
+------------------------------------------+
| 2035-03-15                               |
+------------------------------------------+

Timestamp Values

Beginning with MySQL 8.0.22, the CAST() function supports retrieval of a TIMESTAMP value as being in UTC, using the AT TIMEZONE operator.

Example:

SELECT 
    last_modified,
    CAST(last_modified AT TIME ZONE '+00:00' AS DATETIME) 
FROM users;

Result:

+---------------------+-------------------------------------------------------+
| last_modified       | CAST(last_modified AT TIME ZONE '+00:00' AS DATETIME) |
+---------------------+-------------------------------------------------------+
| 2023-04-03 10:42:40 | 2023-04-03 03:42:40                                   |
+---------------------+-------------------------------------------------------+

In the above example we used '+00:00'. We can alternatively use 'UTC':

SELECT 
    last_modified,
    CAST(last_modified AT TIME ZONE 'UTC' AS DATETIME) 
FROM users;

Result:

+---------------------+----------------------------------------------------+
| last_modified       | CAST(last_modified AT TIME ZONE 'UTC' AS DATETIME) |
+---------------------+----------------------------------------------------+
| 2023-04-03 10:42:40 | 2023-04-03 03:42:40                                |
+---------------------+----------------------------------------------------+

The only supported time zone is UTC, which can be expressed as either '+00:00' or 'UTC'.

If you use 'UTC' and you get an error that reads Unknown or incorrect time zone, it’s probably because your MySQL installation hasn’t yet been configured for named time zones. See How to Set Up Named Time Zones in MySQL for instructions on doing that. Once done, you’ll be able to convert between time zones using the time zone names.

Specify a Character Set

It’s possible to specify a character set to use for the return value when using the CAST() function.

Example:

SELECT 
    COLLATION(123) AS "123",
    COLLATION(CAST(123 AS CHAR CHARACTER SET utf16)) AS "utf16",
    COLLATION(CAST(123 AS CHAR CHARACTER SET latin1)) AS "latin1",
    COLLATION(CAST(123 AS CHAR CHARACTER SET big5)) AS "big5";

Result:

+--------+------------------+-------------------+-----------------+
| 123    | utf16            | latin1            | big5            |
+--------+------------------+-------------------+-----------------+
| binary | utf16_general_ci | latin1_swedish_ci | big5_chinese_ci |
+--------+------------------+-------------------+-----------------+

Here, we used the COLLATION() function to return the collation of each value after it’s been cast to the new data type. Each value uses the default collation for the specified character set.

The initial value is not a string, and so COLLATION() returns binary.

Converting to CHAR without specifying the character set will result in the collation_connection character set collation being used.

Here’s how to get a list of character sets in MySQL.

Specify a Collation

It’s also possible to specify a collation.

Example:

SELECT 
    COLLATION(123) AS "123",
    COLLATION(CAST(123 AS CHAR CHARACTER SET utf16) COLLATE utf16_icelandic_ci) AS "utf16",
    COLLATION(CAST(123 AS CHAR CHARACTER SET latin1) COLLATE latin1_german2_ci) AS "latin1",
    COLLATION(CAST(123 AS CHAR CHARACTER SET big5) COLLATE big5_chinese_nopad_ci) AS "big5";

Result:

+--------+--------------------+-------------------+-----------------------+
| 123    | utf16              | latin1            | big5                  |
+--------+--------------------+-------------------+-----------------------+
| binary | utf16_icelandic_ci | latin1_german2_ci | big5_chinese_nopad_ci |
+--------+--------------------+-------------------+-----------------------+

In this case, we explicitly specified a collation to use that is not the default collation for the specified character set (although still a valid collation for the character set).

Specifying a collation that’s not valid for the character set results in an error.

Here’s what happens when I switch the first two collations around:

SELECT 
    COLLATION(123) AS "123",
    COLLATION(CAST(123 AS CHAR CHARACTER SET utf16) COLLATE latin1_german2_ci) AS "utf16",
    COLLATION(CAST(123 AS CHAR CHARACTER SET latin1) COLLATE utf16_icelandic_ci) AS "latin1",
    COLLATION(CAST(123 AS CHAR CHARACTER SET big5) COLLATE big5_chinese_nopad_ci) AS "big5";

Result:

ERROR 1253 (42000): COLLATION 'latin1_german2_ci' is not valid for CHARACTER SET 'utf16'

It got to the first one and threw an error, because latin1_german2_ci is not a valid collation for the utf16 character set.

See how to return a list of collations available to get a full list of collations and their matching character sets.

Null Arguments

Trying to convert null returns null:

SELECT CAST(null AS DATETIME);

Result:

+------------------------+
| CAST(null AS DATETIME) |
+------------------------+
| NULL                   |
+------------------------+

However, passing null without specifying the new data type results in an error:

SELECT CAST(null);

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

Missing Argument

Calling CAST() without passing an argument results in an error:

SELECT CAST();

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1