In the ISO-8601 calendar, years begin on the first Monday closest to January 1, meaning the start date can fall between December 29 and January 4. This differs from the Gregorian calendar, where years always start on January 1, leading to potential discrepancies around these dates. Additionally, ISO-8601 years, or just ISO years, can be either 52 or 53 weeks long, depending on their starting point.

This article provides four options for extracting the ISO year from a date in DuckDB, accounting for these unique calendar rules.

1. The isoyear() Function

The most obvious option is the isoyear() function. This function does exactly what it promises to do – it gets the ISO year from a given date. All we need to do is provide the date.

Here’s an example:

SELECT 
    isoyear(DATE '2025-12-31') AS '2025-12-31',
    isoyear(DATE '2024-01-01') AS '2024-01-01',
    isoyear(DATE '2023-01-01') AS '2023-01-01',
    isoyear(DATE '2023-01-02') AS '2023-01-02',
    isoyear(DATE '2022-01-01') AS '2022-01-01',
    isoyear(DATE '2022-01-02') AS '2022-01-02',
    isoyear(DATE '2022-01-03') AS '2022-01-03';

Result:

+------------+------------+------------+------------+------------+------------+------------+
| 2025-12-31 | 2024-01-01 | 2023-01-01 | 2023-01-02 | 2022-01-01 | 2022-01-02 | 2022-01-03 |
+------------+------------+------------+------------+------------+------------+------------+
| 2026       | 2024       | 2022       | 2023       | 2021       | 2021       | 2022       |
+------------+------------+------------+------------+------------+------------+------------+

This example uses different dates in order to illustrate that the ISO year doesn’t always align with the Gregorian calendar year.

2. The extract() Function

The extract() function allows us to extract specific parts of a date, such as the year, month, or day. To extract the ISO year, use the isoyear subfield:

SELECT extract('isoyear' FROM DATE '2025-12-31') AS iso_year;

Result:

+----------+
| iso_year |
+----------+
| 2026     |
+----------+

3. The date_part() Function

The date_part() function is similar to extract() and is another option for extracting the ISO year from a date. It takes two arguments: the part of the date we want to extract, and the date itself. In this case, we’ll use isoyear as the first argument:

SELECT date_part('isoyear', DATE '2025-12-31') AS iso_year;

Result:

+----------+
| iso_year |
+----------+
| 2026     |
+----------+

4. The strftime() Function

The strftime() function is typically used for formatting dates as strings, but it can also be used to return specific parts of a date as a formatted string, including the ISO year:

SELECT strftime(DATE '2007-12-31', '%G') AS iso_year;

Result:

+----------+
| iso_year |
+----------+
| 2008     |
+----------+

Here’s a full list of format specifiers that can be used with this function.