ISOYEAR - MaxCompute - Alibaba Cloud Documentation Center

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

Introduction to ISO 8601

The ISO 8601 year is defined by the ISO week date system and differs from the Gregorian calendar year. Specifically, the first day of an ISO year is the Monday of the week that contains the first Thursday of that year. For example:

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

This means an ISO year can start in the last week of the previous Gregorian year or end in the first week of the next Gregorian year. This definition ensures that every ISO year has exactly 52 or 53 full weeks. This simplifies time calculations and cross-year planning.

Syntax

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

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

Parameters

date: Required. The date from which to extract the ISO year. The supported data types are DATE, DATETIME, TIMESTAMP, TIMESTAMP_NTZ, STRING, and INTERVAL.
- For STRING input parameters, the value must be in the yyyy-mm-dd or yyyy-mm-dd hh:mi:ss format.
- For INTERVAL input parameters, only INTERVAL_YEAR_MONTH is supported. INTERVAL_DAY_TIME is not supported. For more information, see INTERVAL data type.

Return value

Returns a value of the INT type. The following rules apply:

If date is not a STRING, DATE, DATETIME, TIMESTAMP, TIMESTAMP_NTZ, or INTERVAL value, or if its format is invalid, NULL is returned or an error is reported.
If date is NULL, NULL is returned.

Examples

Example 1: Extract the ISO year from various date types, such as STRING, DATE, DATETIME, TIMESTAMP, and TIMESTAMP_NTZ.

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

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

-- The input is of the TIMESTAMP type.
-- Returns 2025.
SELECT ISOYEAR(TIMESTAMP '2025-03-31 12:43:00');

-- The input is of the DATE type.
-- Returns 2025.
SELECT ISOYEAR(DATE '2025-01-01');

-- The input is of the DATETIME type.
-- Returns 2024.
SELECT ISOYEAR(DATETIME '2024-01-01 12:30:00');

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

Example 2: Use the INTERVAL_YEAR_MONTH type as a direct input.

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

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

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

Example 3: Calculate the ISO year from the result of an arithmetic operation that combines a date and an INTERVAL.

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

-- The start date 2023-01-01 plus an interval of 1 year and 2 months results in 2024-03-01. This date is in the ISO year 2024.
-- Returns 2024.
SELECT ISOYEAR(DATE '2023-01-01' + INTERVAL '1-2' YEAR TO MONTH);

-- Subtract 9 years to get 2014-01-01. This day is a Wednesday and is in the first week of the ISO year 2014.
-- Returns 2014.
SELECT ISOYEAR(DATE '2023-01-01' + INTERVAL '-9' YEAR);

-- Add 11 months to get 2023-12-01. This date is in the ISO year 2023.
-- Returns 2023.
SELECT ISOYEAR(DATE '2023-01-01' + INTERVAL '11' MONTH);

Example 4: Determine the ISO year at year-end boundaries.

-- 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 the 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 the ISO year 2025.
-- Returns 2025.
SELECT ISOYEAR('2024-12-30');

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

Example 5: Handle a NULL input.

-- If the value of date is NULL, NULL is returned.
SELECT ISOYEAR(NULL);

Related functions

The ISOYEAR function is a date function. For more information about functions for date calculations and conversions, see Date functions.