Working with dates often requires extracting specific components, such as the week number, for analysis or reporting. In DuckDB, there are multiple functions to retrieve the week from a date, each catering to different needs. This article explores four such functions, including how to calculate the ISO week—a standard defined by ISO-8601 where weeks start on Monday and the first week of the year contains the year’s first Thursday.

1. The week() Function

The week() function returns the ISO-8601 week. ISO weeks start on Monday, and the first week of the year is the one that contains at least four days of the new year (i.e., the week with the year’s first Thursday).

SELECT 
    week(DATE '2025-12-31') AS '2025-12-31',
    week(DATE '2024-01-01') AS '2024-01-01',
    week(DATE '2023-01-01') AS '2023-01-01',
    week(DATE '2023-01-02') AS '2023-01-02',
    week(DATE '2022-01-01') AS '2022-01-01',
    week(DATE '2022-01-02') AS '2022-01-02',
    week(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 |
+------------+------------+------------+------------+------------+------------+------------+
| 1          | 1          | 52         | 1          | 52         | 52         | 1          |
+------------+------------+------------+------------+------------+------------+------------+

2. The extract() Function

The extract() function is a versatile tool for retrieving specific parts of a date, such as the year, month, or week. To get the week number from a date, we can use the week parameter. The function also accepts a yearweek parameter, which is used to return the ISO year and week number in YYYYWW format:

SELECT 
    extract('week' FROM DATE '2025-12-31') AS week,
    extract('yearweek' FROM DATE '2025-12-31') AS iso_year_week;

Result:

+------+---------------+
| week | iso_year_week |
+------+---------------+
| 1    | 202601        |
+------+---------------+

3. The date_part() Function

The date_part() function is similar to extract() but uses a slightly different syntax. It allows us to retrieve specific parts of a date, including the week number and/or the ISO year/week:

SELECT 
    date_part('week', DATE '2025-12-31') AS week,
    date_part('yearweek', DATE '2025-12-31') AS iso_year_week;

Result:

+------+---------------+
| week | iso_year_week |
+------+---------------+
| 1    | 202601        |
+------+---------------+

4. strftime() Function

The strftime() function allows us to format dates as strings and extract specific components. We can use the %V format specifier in order to get the ISO week number. We can also use the %W or %U format specifiers to get the Gregorian week number.

• %W: Week number (Monday as the first day of the week).
• %U: Week number (Sunday as the first day of the week).
• %V: ISO 8601 week as a decimal number (Monday as the first day of the week).

SELECT 
    strftime(DATE '2025-12-31', '%U') AS '%U',
    strftime(DATE '2025-12-31', '%W') AS '%W',
    strftime(DATE '2025-12-31', '%V') AS '%V';

Result:

+----+----+----+
| %U | %W | %V |
+----+----+----+
| 52 | 52 | 01 |
+----+----+----+

This function could be useful when we need flexibility in defining the start of the week.

There are plenty of other format specifiers that we can use with this function. We typically combine these format specifiers into a format string to return the date in the format we desire. Here’s a full list of format specifiers that can be used with this function.