4 Functions to Get the ISO Weekday in DuckDB

by

DuckDB provides us with a good selection of functions for working with dates and timestamps. One of the things we might find ourselves needing to do is extracting the ISO weekday from a date or timestamp—a numeric value where Monday is represented as 1 and Sunday as 7.

This article presents four functions we can use to get the ISO weekday from a date in DuckDB.

1. The isodow() Function

The isodow() function is designed specifically to return the ISO weekday from a date. It accepts one parameter; the date for which we want to return the ISO weekday:

SELECT isodow(DATE '2025-04-06') AS 'isodow';

Result:

+--------+
| isodow |
+--------+
| 7      |
+--------+

This is a Sunday (ISO-8601 specifies that Sunday is the 7th day of the week).

Here it is when compared to the dayofweek() function (where Sunday = 0, Saturday = 6):

SELECT 
    isodow(DATE '2025-04-06') AS 'isodow',
    dayofweek(DATE '2025-04-06') AS 'dayofweek',
    dayname(DATE '2025-04-06') AS 'dayname';

Result:

+--------+-----------+---------+
| isodow | dayofweek | dayname |
+--------+-----------+---------+
| 7      | 0         | Sunday  |
+--------+-----------+---------+

I included the dayname() function to confirm that this is indeed a Sunday.

2. The date_part() Function

We can also use the date_part() function to get the ISO day from a date:

SELECT 
    date_part( 'isodow', DATE '2025-04-06' ) AS 'isodow',
    date_part( 'dayofweek', DATE '2025-04-06') AS 'dayofweek',
    dayname(DATE '2025-04-06') AS 'dayname';

Result:

+--------+-----------+---------+
| isodow | dayofweek | dayname |
+--------+-----------+---------+
| 7      | 0         | Sunday  |
+--------+-----------+---------+

3. The extract() Function

The extract() function does a similar job to date_part(), but with a slightly different syntax:

SELECT 
    extract( 'isodow' FROM DATE '2025-04-06' ) AS 'isodow',
    extract( 'dayofweek' FROM DATE '2025-04-06') AS 'dayofweek',
    dayname(DATE '2025-04-06') AS 'dayname';

Result:

+--------+-----------+---------+
| isodow | dayofweek | dayname |
+--------+-----------+---------+
| 7      | 0         | Sunday  |
+--------+-----------+---------+

4. The strftime() Function

The strftime() function is a flexible option for getting the ISO weekday from a date, as well as any other date part that we may wish to extract:

SELECT 
    strftime(DATE '2025-04-06', '%u') AS '%u',
    strftime(DATE '2025-04-06', '%w') AS '%w',
    strftime(DATE '2025-04-06', '%A') AS 'dayname';

Result:

+----+----+---------+
| %u | %w | dayname |
+----+----+---------+
| 7  | 0  | Sunday  |
+----+----+---------+

This function is typically used to format date and timestamps, and so we can pass multiple format specifiers in the format string (in the second argument). Here’s a full list of format specifiers that can be used with this function.