How to Limit a Date Range to Just Business Days in DuckDB

DuckDB enables us to easily generate a range of dates, for example by using the generate_series() function and specifying the start date and end date. But what if you need to limit the output to just those dates that are business days?

This article provides an easy way to get that result.

Example

Here’s an example of getting business days from a range of dates generated by generate_series() in DuckDB:

.mode table
SELECT date_value
FROM unnest(
  generate_series(
    DATE '2025-01-01',
    DATE '2025-01-31',
    INTERVAL '1 day'
  )
) AS t(date_value)
WHERE date_part('dayofweek', date_value) BETWEEN 1 AND 5;

Output:

+---------------------+
|     date_value      |
+---------------------+
| 2025-01-01 00:00:00 |
| 2025-01-02 00:00:00 |
| 2025-01-03 00:00:00 |
| 2025-01-06 00:00:00 |
| 2025-01-07 00:00:00 |
| 2025-01-08 00:00:00 |
| 2025-01-09 00:00:00 |
| 2025-01-10 00:00:00 |
| 2025-01-13 00:00:00 |
| 2025-01-14 00:00:00 |
| 2025-01-15 00:00:00 |
| 2025-01-16 00:00:00 |
| 2025-01-17 00:00:00 |
| 2025-01-20 00:00:00 |
| 2025-01-21 00:00:00 |
| 2025-01-22 00:00:00 |
| 2025-01-23 00:00:00 |
| 2025-01-24 00:00:00 |
| 2025-01-27 00:00:00 |
| 2025-01-28 00:00:00 |
| 2025-01-29 00:00:00 |
| 2025-01-30 00:00:00 |
| 2025-01-31 00:00:00 |
+---------------------+

In the first line I set my DuckDB CLI to table mode in order to output the results in a column. In the actual query, I used generate_series() to generate a range of dates, and I used the unnest() function to unnest the result of generate_series().

The WHERE clause is what narrows the result down to just week days. The date_part() function returns the part of the date that we specify. So we’re able to use this function to filter the query to just those dates that have a dayofweek value of between 1 and 5.

Including the Day Name

We can expand on the above query and include the day name in the result. This can help confirm to us that it is indeed just week days being returned:

SELECT 
  date_value,
  dayname(date_value)
FROM unnest(
  generate_series(
    DATE '2025-01-01',
    DATE '2025-01-31',
    INTERVAL '1 day'
  )
) AS t(date_value)
WHERE date_part('dayofweek', date_value) BETWEEN 1 AND 5;

Output:

+---------------------+---------------------+
|     date_value      | dayname(date_value) |
+---------------------+---------------------+
| 2025-01-01 00:00:00 | Wednesday           |
| 2025-01-02 00:00:00 | Thursday            |
| 2025-01-03 00:00:00 | Friday              |
| 2025-01-06 00:00:00 | Monday              |
| 2025-01-07 00:00:00 | Tuesday             |
| 2025-01-08 00:00:00 | Wednesday           |
| 2025-01-09 00:00:00 | Thursday            |
| 2025-01-10 00:00:00 | Friday              |
| 2025-01-13 00:00:00 | Monday              |
| 2025-01-14 00:00:00 | Tuesday             |
| 2025-01-15 00:00:00 | Wednesday           |
| 2025-01-16 00:00:00 | Thursday            |
| 2025-01-17 00:00:00 | Friday              |
| 2025-01-20 00:00:00 | Monday              |
| 2025-01-21 00:00:00 | Tuesday             |
| 2025-01-22 00:00:00 | Wednesday           |
| 2025-01-23 00:00:00 | Thursday            |
| 2025-01-24 00:00:00 | Friday              |
| 2025-01-27 00:00:00 | Monday              |
| 2025-01-28 00:00:00 | Tuesday             |
| 2025-01-29 00:00:00 | Wednesday           |
| 2025-01-30 00:00:00 | Thursday            |
| 2025-01-31 00:00:00 | Friday              |
+---------------------+---------------------+