:FAQ about built-in functions

How do I convert a date like 2010/1/3 to 2010-01-03?

For zero-padded input (e.g., 2010/01/03), combine TO_DATE and TO_CHAR:

SELECT TO_CHAR(TO_DATE('2010/01/03', 'yyyy/mm/dd'), 'yyyy-mm-dd');
-- Returns: 2010-01-03

For non-zero-padded input (e.g., 2010/1/3), built-in functions cannot parse it directly. Write a user-defined function (UDF). This topic describes the types, scenarios, development process, and usage notes of UDFs that are supported in MaxCompute.") to handle variable-length date parts.

For more information, see TO_DATE and TO_CHAR.

How do I convert a UNIX timestamp to a DATETIME value?

Use FROM_UNIXTIME:

SELECT FROM_UNIXTIME(1609459200);
-- Returns: 2021-01-01 08:00:00

For more information, see FROM_UNIXTIME.

How do I get the current system time?

Use GETDATE:

SELECT GETDATE();
-- Returns the current date and time, e.g., 2026-02-15 10:30:00

For more information, see GETDATE.

Why do I get a "cannot be resolved" error with YEAR, QUARTER, MONTH, or DAY?

FAILED: ODPS-0130071:[1,8] Semantic analysis exception - function or view 'year' cannot be resolved

YEAR, QUARTER, MONTH, and DAY are extension functions introduced in MaxCompute V2.0. They require the V2.0 data type edition.

Add the following setting before your SQL statement:

SET odps.sql.type.system.odps2 = true;
SELECT YEAR(GETDATE());

Why does TO_DATE fail with a "missing minute part" error?

FAILED: ODPS-0121095:Invalid arguments - format string has second part, but doesn't have minute part : yyyy-MM-dd HH:mm:ss

In MaxCompute, both mm and MM represent the month. Use mi for minutes:

-- Incorrect
SELECT TO_DATE('2016-07-18 18:18:18', 'yyyy-MM-dd HH:mm:ss');

-- Correct
SELECT TO_DATE('2016-07-18 18:18:18', 'yyyy-MM-dd HH:mi:ss');

This differs from many other SQL platforms where mm represents minutes. In MaxCompute, always use mi for the minute component.

Why does ROUND(4.515, 2) return 4.51 instead of 4.52?

DOUBLE values are 8-byte floating-point numbers with limited precision. The value 4.515 is stored internally as approximately 4.514999999..., so rounding to two decimal places produces 4.51:

SELECT ROUND(4.515, 2), ROUND(125.315, 2);
-- Returns: 4.51, 125.32

For exact rounding, use the DECIMAL type:

SELECT ROUND(4.515BD, 2);
-- Returns: 4.52

The DECIMAL type stores values with exact precision, avoiding floating-point representation issues.

How do I generate an auto-increment sequence?

Use ROW_NUMBER as a window function:

SELECT ROW_NUMBER() OVER (ORDER BY id) AS row_num, *
FROM my_table;

For more information, see ROW_NUMBER.

How do I concatenate values in a column?

Use WM_CONCAT:

SELECT WM_CONCAT(',', name) AS all_names
FROM my_table;
-- Returns: Alice,Bob,Charlie

For more information, see WM_CONCAT.

Does MaxCompute support MD5?

Yes. MD5 computes the hash of a string:

SELECT MD5('hello');
-- Returns: 5d41402abc4b2a76b9719d911017c592

For more information, see MD5.

How do I left pad a string with zeros?

Use LPAD:

SELECT LPAD('42', 6, '0');
-- Returns: 000042

For more information, see LPAD.

Does MaxCompute support SUBSTRING_INDEX?

Yes. SUBSTRING_INDEX works the same way as in MySQL:

SELECT SUBSTRING_INDEX('www.example.com', '.', 2);
-- Returns: www.example

For more information, see SUBSTRING_INDEX.

Does REGEXP_COUNT support nested queries in the pattern parameter?

No. The pattern parameter of REGEXP_COUNT does not support nested query statements.

For more information, see REGEXP_COUNT.

Does MaxCompute support Oracle's TO_CHAR number formatting?

MaxCompute does not support Oracle's TO_CHAR(Data, FM9999.00) syntax. Use FORMAT_NUMBER instead:

SELECT FORMAT_NUMBER(12332.123456, '#,###,###,###.###');
-- Returns: 12,332.123

For more information, see FORMAT_NUMBER.

How do I aggregate JSON fields that match a condition?

Filter records with SQL conditions such as LIKE, then use ARRAY or MAP to construct a complex type from the results. Convert the result to a JSON string with TO_JSON:

SELECT TO_JSON(ARRAY(col1, col2))
FROM my_table
WHERE col1 LIKE '%keyword%';

How do I extract JSON keys as separate columns?

Use GET_JSON_OBJECT:

SELECT
  GET_JSON_OBJECT(json_col, '$.name') AS name,
  GET_JSON_OBJECT(json_col, '$.age') AS age
FROM my_table;

For more information, see GET_JSON_OBJECT.

How do I convert a JSON string to an array?

Use FROM_JSON with the target type as a string:

SELECT FROM_JSON(json_col, 'array<bigint>');
-- Converts a JSON array string like "[1, 2, 3]" to a MaxCompute ARRAY<BIGINT>

For more information, see FROM_JSON.

MaxCompute does not support IFNULL. What should I use instead?

MaxCompute does not have an IFNULL function. If you try to use it, you get:

Semantic analysis exception - Invalid function : line 1:41 'ifnull'

Use one of these alternatives:

For most cases, NVL is the simplest drop-in replacement:

-- MySQL
SELECT IFNULL(col, 0) FROM my_table;

-- MaxCompute equivalent
SELECT NVL(col, 0) FROM my_table;

For more information, see NVL, COALESCE, CASE WHEN expression, and function mappings between MaxCompute, Hive, MySQL, and Oracle.

How do I split one row into multiple rows?

Use TRANS_COLS. The output includes an index column followed by the transposed data columns, so the number of aliases must equal 1 + the number of data columns:

SELECT TRANS_COLS(2, col1, col2) AS (idx, key, value)
FROM my_table;

For more information, see TRANS_COLS.

Why do I get "Expression not in GROUP BY key" when using COALESCE?

FAILED: ODPS-0130071:Semantic analysis exception - Expression not in GROUP BY key : line 8:9 "$.table"

This error occurs when a non-aggregated expression inside COALESCE references a column not listed in GROUP BY.

In the following example, the query fails because get_json_object(extended_x, '$.table') inside decode is not aggregated and not in GROUP BY:

SELECT
  md5(concat(aid, bid)) AS id,
  aid,
  bid,
  sum(amountdue) AS amountdue,
  coalesce(
    sum(regexp_count(get_json_object(extended_x, '$.table.tableParties'), '{')),
    decode(get_json_object(extended_x, '$.table'), null, 0, 1)
  ) AS tableparty
FROM e_orders
WHERE pt = '20170425'
GROUP BY aid, bid;

The decode(get_json_object(extended_x, '$.table'), null, 0, 1) expression operates on individual rows but appears outside any aggregate function. Either wrap it in an aggregate function or include it in the GROUP BY clause.

Why do I get implicit type conversion errors after enabling MaxCompute V2.0?

When the MaxCompute V2.0 data type edition is enabled (odps.sql.type.system.odps2=true), the following implicit type conversions are disabled:

These conversions can cause precision loss, so V2.0 blocks them by default. To resolve this:

• Explicit conversion (recommended): Use CAST to convert types explicitly: For more information, see CAST.

    SELECT CAST(string_col AS BIGINT) FROM my_table;

• Disable V2.0 data types: Set odps.sql.type.system.odps2=false to re-enable implicit conversions. Note that this setting may also disable V2.0 extension functions like YEAR, QUARTER, MONTH, and DAY, depending on the project-level configuration.