Watch the 7.3 Webinar On-Demand
This new release brings updates to Universal Storage, query
optimization, and usability that you won’t want to miss.
CAST and CONVERT Functions Casts the input to the given datatype. There is usually no visible effect on the printed value; what changes is the rules for comparison and sorting.
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]})
The return type is the datatype that was specified in the command.
SELECT CONVERT(-123, UNSIGNED);
+-------------------------+
| CONVERT(-123, UNSIGNED) |
+-------------------------+
| 18446744073709551493 |
+-------------------------+
SELECT '2019-01-01', CAST('2019-01-01' AS TIME);
+------------+----------------------------+
| 2019-01-01 | CAST('2019-01-01' AS TIME) |
+------------+----------------------------+
| 2019-01-01 | 00:20:19 |
+------------+----------------------------+
:>
and !:>
**Two operators are provided for data type casting, including
:>
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.
The data conversion behavior of !:>
is the same as for
INSERT IGNORE.
SELECT (1 :> DECIMAL(18,2));
+----------------------+
| (1 :> DECIMAL(18,2)) |
+----------------------+
| 1.00 |
+----------------------+
SELECT (NULL :> DECIMAL(18,2) NOT NULL);
ERROR 2222 (HY000): Tried to convert NULL to a NOT NULL type
SELECT (NULL !:> DECIMAL(18,2) NOT NULL);
+-----------------------------------+
| (NULL !:> DECIMAL(18,2) NOT NULL) |
+-----------------------------------+
| 0.00 |
+-----------------------------------+
The third example, using !:>
, succeeds and produces
a zero value, whereas the second example fails.