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.

CONVERT

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.

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 that was specified in the command.

Examples

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                   |
+------------+----------------------------+

CAST Operators :> 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.

Examples of CAST Operators

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.