You are viewing an older version of this section. View current production version.
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.
