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

memsql> select convert(-123, UNSIGNED);
+-------------------------+
| convert(-123, UNSIGNED) |
+-------------------------+
|    18446744073709551493 |
+-------------------------+

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

memsql> select (1 :> decimal(18,2));
+----------------------+
| (1 :> decimal(18,2)) |
+----------------------+
|                 1.00 |
+----------------------+

memsql> select (null :> decimal(18,2) not null);
ERROR 2222 (HY000): Tried to convert NULL to a NOT NULL type

memsql> 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 useful?