CAST or CONVERT

Note

Both the CAST and CONVERT commands perform the same operation.

Casts the input to the given data type.

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(length) | CHAR(length) | DATE | DATETIME[(prec)] |
DECIMAL[(prec [, scale])] | TIME[(prec)] | SIGNED [INTEGER] | UNSIGNED [INTEGER]})
CAST (input AS {BINARY(length) | CHAR(length) | DATE | DATETIME[(prec)] |
DECIMAL[(prec [, scale])] | TIME[(prec)] | SIGNED [INTEGER] | UNSIGNED [INTEGER]})

Return Type

The return type is the data type 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                          |
+------------+-------------------------------------+
SELECT CAST(123 AS CHAR(2));
+-------------------------+
| CAST(123 AS CHAR(2))    |
+-------------------------+
|    12                   |
+-------------------------+

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. The data conversion behavior of !:> is the same as for INSERT IGNORE.

Note

For a command that uses a casting operator, you can specify any data type 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 type

In the following example, :> operator is used with a string data type for case-sensitive search:

SELECT * FROM Tabview WHERE element_name = "var" :> VARCHAR (50) COLLATE utf8_bin;
+------+--------------+
| id   | element_name |
+------+--------------+
|   20 | var          |
+------+--------------+

Last modified: September 3, 2024

Was this article helpful?