ISOYEAR - MaxCompute - Alibaba Cloud Documentation Center

The ISOYEAR function extracts the ISO 8601 year from a specified date.

Introduction to ISO 8601

The ISO year follows the ISO week date system, which differs from the Gregorian calendar year. The first day of an ISO year is the Monday of the week that contains January 4th — equivalently, the Monday of the week containing the first Thursday of that year.

If January 1 falls on a Friday, Saturday, or Sunday, that week belongs to the last week of the previous ISO year.
If January 1 falls on a Monday, Tuesday, Wednesday, or Thursday, that week belongs to the first week of the new ISO year.

As a result, an ISO year always spans exactly 52 or 53 full weeks, but its boundaries can cross into the adjacent Gregorian calendar year.

For consistent results in weekly reporting, use ISOYEAR together with ISO week functions. Using ISOYEAR alone with a Gregorian week function can produce mismatched year and week values at year boundaries.

Syntax

INT ISOYEAR(STRING|DATE|DATETIME|TIMESTAMP|TIMESTAMP_NTZ|INTERVAL <date>)

-- Returns 2025.
SELECT ISOYEAR(DATE '2025-03-12');

Parameters

date: Required. The date from which to extract the ISO year. Supported data types: DATE, DATETIME, TIMESTAMP, TIMESTAMP_NTZ, STRING, and INTERVAL.

STRING: The value must be in yyyy-mm-dd or yyyy-mm-dd hh:mi:ss format.
INTERVAL: Only INTERVAL_YEAR_MONTH is supported. INTERVAL_DAY_TIME is not supported. For details, see INTERVAL data type.

Return value

Returns an INT value.

If date is NULL, NULL is returned.
If date is an unsupported type or has an invalid format, NULL is returned or an error is reported.

Examples

Example 1: Extract the ISO year from various date types

-- Enable MaxCompute V2.0 data types.
SET odps.sql.type.system.odps2 = true;

-- STRING input. Strings in standard date formats are supported.
-- Returns 2025.
SELECT ISOYEAR('2025-03-12 12:22:00.123');

-- TIMESTAMP input.
-- Returns 2025.
SELECT ISOYEAR(TIMESTAMP '2025-03-31 12:43:00');

-- DATE input.
-- Returns 2025.
SELECT ISOYEAR(DATE '2025-01-01');

-- DATETIME input.
-- Returns 2024.
SELECT ISOYEAR(DATETIME '2024-01-01 12:30:00');

-- TIMESTAMP_NTZ (timestamp without time zone) input. Microsecond precision is supported.
-- Returns 2025.
SELECT ISOYEAR(TIMESTAMP_NTZ '2025-06-06 12:43:00.123');

Example 2: Use INTERVAL_YEAR_MONTH as input

-- Enable MaxCompute V2.0 data types.
SET odps.sql.type.system.odps2 = true;

-- An interval of 1 year and 2 months. The year component is 1.
-- Returns 1.
SELECT ISOYEAR(INTERVAL '1-2' YEAR TO MONTH);

-- INTERVAL '14' MONTH is equivalent to INTERVAL '1-2' YEAR TO MONTH.
-- Returns 1.
SELECT ISOYEAR(INTERVAL '14' MONTH);

Example 3: Extract the ISO year from a date arithmetic result

Each example adds an INTERVAL to a base date and extracts the ISO year of the result.

-- Enable MaxCompute V2.0 data types.
SET odps.sql.type.system.odps2 = true;

-- 2023-01-01 + 1 year 2 months = 2024-03-01, which is in ISO year 2024.
-- Returns 2024.
SELECT ISOYEAR(DATE '2023-01-01' + INTERVAL '1-2' YEAR TO MONTH);

-- 2023-01-01 - 9 years = 2014-01-01, a Wednesday in the first week of ISO year 2014.
-- Returns 2014.
SELECT ISOYEAR(DATE '2023-01-01' + INTERVAL '-9' YEAR);

-- 2023-01-01 + 11 months = 2023-12-01, which is in ISO year 2023.
-- Returns 2023.
SELECT ISOYEAR(DATE '2023-01-01' + INTERVAL '11' MONTH);

Example 4: ISO year at year-end boundaries

The following examples show how calendar year boundaries map to ISO years. Two dates just one day apart can fall in different ISO years.

-- Enable MaxCompute V2.0 data types.
SET odps.sql.type.system.odps2 = true;

-- January 1, 2023 is a Sunday — it belongs to the last week of ISO year 2022.
-- Returns 2022.
SELECT ISOYEAR(DATETIME '2023-01-01 12:30:00');

-- December 30, 2024 is a Monday — it belongs to the first week of ISO year 2025.
-- Returns 2025.
SELECT ISOYEAR('2024-12-30');

-- January 1, 2025 is a Wednesday — it belongs to the first week of ISO year 2025.
-- Returns 2025.
SELECT ISOYEAR('2025-01-01 00:00:00');

Boundary contrast: 2006-01-01 (Sunday) returns ISO year 2005, while 2006-01-02 (Monday) returns ISO year 2006. A single day's difference crosses the ISO year boundary.

Example 5: Handle a NULL input

-- Returns NULL.
SELECT ISOYEAR(NULL);

Related functions

ISOYEAR is a date function. For other date calculation and conversion functions, see Date functions.