Skip to content

CAST

Function Description

The CAST() function can convert one value of any type into another specific type.

Grammar Structure

> CAST(value AS datatype)
Parameters Description
value Required parameters, value to be converted
datatype Required parameters, target data type

Currently, cast can be converted as follows:

  • Conversion between numeric types mainly includes SIGNED, UNSIGNED, FLOAT, DOUBLE types
  • Numeric type to character CHAR type conversion
  • Convert character types with numeric format to numeric type (negative numbers need to be converted to SIGNED)
  • Time type (including Date, Datetime, Timestamp and Time) is converted to INT type, with decimal places rounded
  • Time type (including Date, Datetime, Timestamp and Time) is converted to fixed-point type, retaining decimal places

For detailed data type conversion rules, see [Data type conversion] (../../../Data-Types/data-type-conversion.md).

Example

drop table if exists t1;
CREATE TABLE t1 (a int,b float,c char(1),d varchar(15));
INSERT INTO t1 VALUES (1,1.5,'1','-2');

mysql> SELECT CAST(a AS FLOAT) a_cast,CAST(b AS UNSIGNED) b_cast,CAST(c AS SIGNED) c_cast, CAST(d AS SIGNED) d_cast from t1;
+------------------+----------+
| a_cast | b_cast | c_cast | d_cast |
+------------------+----------+
| 1 | 2 | 1 | -2 |
+------------------+----------+
1 row in set (0.01 sec)

mysql> SELECT CAST(a AS CHAR) a_cast, CAST(b AS CHAR) b_cast,CAST(c AS DOUBLE) c_cast, CAST(d AS FLOAT) d_cast from t1;
+------------------+----------+
| a_cast | b_cast | c_cast | d_cast |
+------------------+----------+
| 1 | 1.5 | 1 | -2 |
+------------------+----------+
1 row in set (0.00 sec)

limit

  • Character types that are not numerical cannot be converted to numerical types.
  • Character types cannot be converted to Date type.
  • Date, Datetime type cannot be converted to character type.
  • Date and Datetime cannot convert each other.