MaxCompute SQL allows conversion between data types. The two conversion methods are explicit type conversion and implicit type conversion.
Y means can be converted. N means cannot be converted. – means conversion is not required.
select cast(user_id as double) as new_id from user; select cast('2015-10-01 00:00:00' as datetime) as new_date from user;
Implicit conversions and scopes
T means can be converted. F means cannot be converted.
select user_id+age+'12345', concat(user_name,user_id,age) from user;
- Implicit conversions under relational operators
Relational operators include equal to (=), not equal to (<>), less than (<), less than or equal to (<=), greater than (>), greater than or equal to (>=), IS NULL, IS NOT NULL, LIKE, RLIKE, and IN. For the particularities, implicit conversion rules of LIKE, RLIKE, and IN are discussed separately. The following descriptions do not contain these three special operators.The following table describes implicit conversion rules when different types of data is involved in relational operations.
From/To Bigint Double String Datetime Boolean Decimal Bigint – Double Double N N Decimal Double Double – Double N N Decimal String Double Double – Datetime N Decimal Datetime N N Datetime – N N Boolean N N N N – N Decimal Decimal Decimal Decimal N N - Note
- If two types cannot be implicitly converted, the relational operation is aborted with an error.
- For more information about the relational operators, see Relational Operators.
- Implicit conversions under special relational operators
Special relational operators include LIKE, RLIKE, and IN.
- The usage of LIKE and RLIKE is as follows:
source like pattern; source rlike pattern;The following illustrates the notes for LIKE and RLIKE in implicit conversions:
- The source and pattern parameters of LIKE and RLIKE can only be of the String type.
Other types cannot be involved in the operation, nor be implicitly converted to the String type.
The usage of IN is as follows:
key in (value1, value2, …)Implicit conversion rules of IN:
- Data in the value column must be consistent.
- To compare keys and values, if Bigint, Double, and String types are compared, convert them to Double type. If the Datetime and String types are compared, convert them to Datetime type. Conversions between other types are not allowed.
- The usage of LIKE and RLIKE is as follows:
- Implicit conversions under arithmetic operators
Arithmetic operators include addition (+), subtraction (-), multiplication (*), division (/), modulo (%), unary plus (+), and unary minus (-). Their implicit conversion rules are described as follows:
- Only the String, Bigint, Double, and Decimal types can be involved in the operation.
- The String type are implicitly converted to the Double type before the operation.
- When the Bigint and Double types are involved in the operation, the Bigint type is implicitly converted to the Double type.
- The Datetime and Boolean types are not allowed in the arithmetic operation.
- Implicit conversions under logical operators
Logical operators include AND, OR, and NOT. Their implicit conversion rules are as follows:
- Only the Boolean type can be involved in the logical operation.
- Other types are not allowed in the logical operation, and cannot be implicitly converted to other types.
Implicit Conversions for Built-in Functions
- To call a function, if the data type of an input parameter is different from that defined in the function, convert the data type of the input parameter to that defined in the function.
- Parameters of different built-in functions of MaxCompute SQL have different requirements on implicit conversions. For more information, see Built-in Functions.
Implicit Conversions under CASE WHEN
- If the types of the returned values are Bigint and Double, convert all to the Double type.
- If a String type exists in return types, convert all to the String type. If the conversion fails (such as Boolean type conversion), an error is returned.
- Conversions between other types are not allowed.
Conversions between the String Type and Datetime Type
MaxCompute supports conversions between the String type and Datetime type. The conversion format is
|Unit||String (case-insensitive)||Valid value 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|
cast("2013/12/31 02/34/34" as datetime) cast("20131231023434" as datetime) cast("2013-12-31 2:34:34" as datetime)
cast("2013-02-29 12:12:12" as datetime) -- Returns an error because February 29, 2013 does not exist. cast("2013-11-31 12:12:12" as datetime) -- Returns an exception because November 31, 2013 does not exist.
MaxCompute provides the TO_DATE function to convert the String type that does not meet the Datetime format to the Datetime type. For more information, see TO_DATE.