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