Outdated Version

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.