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.

Was this Article Helpful?

Thanks For Your Feedback!