All Products
Search

This Product

    MaxCompute:Data type conversions

    Document Center

    MaxCompute:Data type conversions

    Last Updated:Mar 26, 2026

    MaxCompute SQL supports two forms of type conversion: explicit conversion using the CAST function, and implicit conversion where MaxCompute converts types automatically based on context.

    Explicit conversion

    The CAST function converts a value from one data type to another. For syntax details, see CAST.

    The following table shows supported explicit conversions. Y = supported, N = not supported, N/A = not applicable.

    From\To BIGINT DOUBLE STRING DATETIME BOOLEAN DECIMAL FLOAT
    BIGINT N/A Y Y N Y Y Y
    DOUBLE Y N/A Y N Y Y Y
    STRING Y Y N/A Y Y Y Y
    DATETIME N N Y N/A N N N
    BOOLEAN Y Y Y N N/A Y Y
    DECIMAL Y Y Y N Y N/A Y
    FLOAT Y Y Y N Y Y N/A

    Attempting an unsupported conversion returns an error.

    Conversion behavior and limits

    Numeric conversions

    Conversion Behavior Example
    DOUBLE → BIGINT Decimal portion is truncated, not rounded CAST(1.6 AS BIGINT) → 1
    STRING (DOUBLE format) → BIGINT Converted STRING → DOUBLE → BIGINT; decimal portion truncated CAST('1.6' AS BIGINT) → 1
    STRING (BIGINT format) → DOUBLE One decimal place is added CAST('1' AS DOUBLE) → 1.0

    DATETIME conversions

    Conversions involving DATETIME use the format yyyy-mm-dd hh:mi:ss. Leading zeros are required: 2014-01-09 12:12:12 is valid; 2014-1-9 12:12:12 is not.

    DECIMAL conversions

    • Casting STRING to DECIMAL returns an error if the value exceeds the DECIMAL range—for example, if the most significant digit overflows or the least significant digit is truncated.

    • Casting DECIMAL to DOUBLE or FLOAT loses precision. For financial calculations such as bill amounts or premium rates, keep values in DECIMAL.

    Conversions not supported by CAST

    Some type pairs cannot be converted with CAST but can be handled with built-in functions:

    Conversion Alternative function
    BOOLEAN → STRING TO_CHAR
    STRING (non-standard format) → DATETIME TO_DATE

    Complex type conversions

    Conversions between complex types (ARRAY, STRUCT) follow the conversion rules of their element types or fields:

    • Implicit conversion between complex types is supported only if the subtypes support implicit conversion.

    • Explicit conversion between complex types is supported only if the subtypes support explicit conversion.

    • For STRUCT types, field names can differ, but the field count must match and each field pair must support the required conversion.

    Conversion Implicit Explicit
    BIGINT → STRING Y Y
    BIGINT → INT N Y
    BIGINT → DATETIME N N
    STRUCT<a:BIGINT, b:INT> → STRUCT<col1:STRING, col2:BIGINT> Y Y
    STRUCT<a:BIGINT, b:INT> → STRUCT<col1:STRING> (field count mismatch) N N

    Examples

    Note

    The following examples use a user table. To create the table and insert sample data, run:

    CREATE TABLE IF NOT EXISTS user (
  user_name STRING,
  user_id   BIGINT,
  age       BIGINT
);
INSERT INTO user VALUES ('zhangsan', 111, 20);
    -- Cast a BIGINT column to DOUBLE
SELECT CAST(user_id AS DOUBLE) AS new_id FROM user;

-- Cast a string literal to DATETIME
SELECT CAST('2015-10-01 00:00:00' AS DATETIME) AS new_date;

-- Cast an integer array to a string array
SELECT CAST(ARRAY(1, 2, 3) AS ARRAY<STRING>);

-- Join a string array into a comma-separated string
SELECT CONCAT_WS(',', CAST(ARRAY(1, 2) AS ARRAY<STRING>));

    Implicit conversion

    MaxCompute automatically converts data types based on context. The following tables show supported implicit conversions. Y = supported, N = not supported, N/A = not applicable.

    Integer and floating-point types

    From\To BOOLEAN TINYINT SMALLINT INT BIGINT FLOAT
    BOOLEAN Y N N N N N
    TINYINT N Y Y Y Y Y
    SMALLINT N N Y Y Y Y
    INT N N Y Y Y Y
    BIGINT N N N N Y Y
    FLOAT N N N N Y Y

    Decimal and string types

    From\To DOUBLE DECIMAL STRING VARCHAR TIMESTAMP BINARY
    DOUBLE Y Y Y Y N N
    DECIMAL N Y Y Y N N
    STRING Y Y Y Y N N
    VARCHAR Y Y N N N/A N/A
    TIMESTAMP N N Y Y Y N
    BINARY N N N N N Y

    Attempting an unsupported implicit conversion, or a conversion that fails at runtime, returns an error.

    Note

    • MaxCompute V2.0 introduced typed constants for DECIMAL and DATETIME. For example, 100BD is a DECIMAL constant with value 100, and 2017-11-11 00:00:00 is a DATETIME constant. These constants can be used directly in VALUES clauses and tables.

    • When types don't match in an expression, use CAST to convert explicitly rather than relying on implicit conversion.

    • Implicit conversion rules apply to specific contexts. In some situations, only a subset of the rules take effect.

    Example

    SELECT user_id + age + '12345', CONCAT(user_name, user_id, age) FROM user;

    In this query, '12345' is implicitly converted to DOUBLE for the arithmetic expression, and user_id and age are implicitly converted to STRING for CONCAT.

    Implicit conversions with relational operators

    Relational operators include =, <>, <, <=, >, >=, IS NULL, and IS NOT NULL. The following table shows how MaxCompute resolves type mismatches in relational comparisons. The cell value is the type both operands are converted to.

    From\To BIGINT DOUBLE STRING DATETIME BOOLEAN DECIMAL
    BIGINT N/A DOUBLE DOUBLE N N DECIMAL
    DOUBLE DOUBLE N/A DOUBLE N N DECIMAL
    STRING DOUBLE DOUBLE N/A DATETIME N DECIMAL
    DATETIME N N DATETIME N/A N N
    BOOLEAN N N N N N/A N
    DECIMAL DECIMAL DECIMAL DECIMAL N N N/A

    If the two operands don't support implicit conversion, the comparison fails with an error. For more information about relational operators, see Operators.

    Implicit conversions with LIKE, RLIKE, and IN

    LIKE, RLIKE, and IN follow different rules from other relational operators.

    LIKE and RLIKE

    source LIKE pattern;
source RLIKE pattern;

    Both source and pattern must be STRING. Other types cannot be used and are not implicitly converted to STRING.

    IN

    key IN (value1, value2, ...)

    All values in the list must be of the same type. When the list contains mixed types, MaxCompute applies these conversions:

    • If the list contains BIGINT, DOUBLE, and STRING values, BIGINT and STRING are converted to DOUBLE.

    • If the list contains DATETIME and STRING values, STRING is converted to DATETIME.

    • Other type combinations are not supported.

    Implicit conversions with arithmetic operators

    Arithmetic operators include +, -, *, /, and %.

    • Only BIGINT, DOUBLE, DECIMAL, and STRING values can be used in arithmetic expressions.

    • STRING is implicitly converted to DOUBLE before the operation.

    • When BIGINT and DOUBLE appear in the same expression, BIGINT is converted to DOUBLE.

    • DATETIME and BOOLEAN cannot be used in arithmetic expressions.

    Implicit conversions with logical operators

    Logical operators include AND, OR, and NOT. Only BOOLEAN values can be used in logical expressions. Other types are not implicitly converted.

    Implicit conversions with built-in functions

    When a built-in function receives an argument whose type differs from the expected parameter type, MaxCompute implicitly converts the argument to the expected type. Each built-in function defines its own implicit conversion requirements.

    Implicit conversions with CASE WHEN

    For more information about CASE WHEN, see CASE WHEN expression.

    When the THEN and ELSE branches return different types, MaxCompute resolves them as follows:

    • BIGINT and DOUBLE → all branches converted to DOUBLE.

    • Any branch returns STRING → all branches converted to STRING. If a branch cannot be converted (for example, BOOLEAN to STRING), an error is returned.

    • Other type combinations are not supported.

    Conversions between STRING and DATETIME

    Conversions between STRING and DATETIME use the format yyyy-mm-dd hh:mi:ss. The valid range for each component is:

    Component Format Valid range
    Year yyyy 0001–9999
    Month mm 01–12
    Day dd 01–28/29/30/31
    Hour hh 00–23
    Minute mi 00–59
    Second ss 00–59

    Leading zeros are required. For example, 2014-1-9 12:12:12 is invalid; write it as 2014-01-09 12:12:12.

    -- Valid: cast a string to DATETIME
SELECT CAST('2013-12-31 02:34:34' AS DATETIME);

-- Converting DATETIME to STRING produces the yyyy-mm-dd hh:mi:ss format automatically

    To convert a STRING that doesn't follow this format, use TO_DATE.

    Conversion attempts such as the following fail and cause an exception.

    CAST("2013/12/31 02/34/34" AS DATETIME)  
CAST("20131231023434" AS DATETIME)  
CAST("2013-12-31 2:34:34" AS DATETIME)

    The maximum value for the day component depends on the actual number of days in the specified month. If the value exceeds the number of days in that month, an exception occurs.

    CAST("2013-02-29 12:12:12" AS DATETIME)      -- Exception returned. February 2013 does not have 29 days.
CAST("2013-11-31 12:12:12" AS DATETIME)      -- Exception returned. November 2013 does not have 31 days.