CAST or CONVERT
On this page
Note
Both the CAST and CONVERT commands perform the same operation.
Casts the input to the given datatype.
There is usually no visible effect on the printed value; there is a change only in the rules for comparison and sorting.
Syntax
CONVERT (input, {BINARY | CHAR | DATE | DATETIME[(prec)] |DECIMAL[(prec [, scale])] | TIME[(prec)] | SIGNED [INTEGER] | UNSIGNED [INTEGER]})CAST (input AS {BINARY | CHAR | DATE | DATETIME[(prec)] |DECIMAL[(prec [, scale])] | TIME[(prec)] | SIGNED [INTEGER] | UNSIGNED [INTEGER]})
Return Type
The return type is the datatype specified in the command.
Examples
SELECT CONVERT(-123, UNSIGNED);
+-------------------------+
| CONVERT(-123, UNSIGNED) |
+-------------------------+
| 18446744073709551493 |
+-------------------------+SELECT '2019-01-01', CAST('2019-01-01 16:15:00' AS DATE);
+------------+-------------------------------------+
| 2019-01-01 | CAST('2019-01-01 16:15:00' AS DATE) |
+------------+-------------------------------------+
| 2019-01-01 | 2019-01-01 |
+------------+-------------------------------------+Cast Operators :> and !:>
Two operators are provided for data type casting - :> for casting with the default error behavior, and !:> which does a "forceful" cast that always succeeds and produces the standard default value for the target data type even if the cast would otherwise produce an error. !:> is the same as for INSERT IGNORE.
Note
For a command that uses a casting operator, you can specify any datatype after the casting operator :> or !:> to convert the input value, same as you would specify a column type in a CREATE TABLE statement.
Examples
SELECT (1 :> DOUBLE);
+---------------+
| (1 :> DOUBLE) |
+---------------+
| 1 |
+---------------+
1 row in set (0.015 sec)SELECT (1 :> DECIMAL(18,2));
+----------------------+
| (1 :> DECIMAL(18,2)) |
+----------------------+
| 1.00 |
+----------------------+SELECT '2019-01-01' !:> DATETIME;
+---------------------------+
| '2019-01-01' !:> DATETIME |
+---------------------------+
| 2019-01-01 00:00:00 |
+---------------------------+In the following example, the SELECT command returns an error using :>, while it succeeds and produces a zero value using !:>:
SELECT (NULL :> DECIMAL(18,2) NOT NULL);
ERROR 2222 (HY000): Tried to convert NULL to a NOT NULL typeIn the following example, :> operator is used with a string datatype for case-sensitive search:
SELECT * FROM Tabview WHERE element_name = "var" :> VARCHAR (50) COLLATE utf8_bin;
+------+--------------+
| id | element_name |
+------+--------------+
| 20 | var |
+------+--------------+Last modified: July 18, 2023