When working with dates in DuckDB, extracting specific components like the day is a common task. DuckDB provides a good range of functions that we can use to help us to perform such a task.

This article presents seven different functions we can use to extract the day from a date in DuckDB.

1. Using the extract() Function

The extract() function is a versatile tool in DuckDB that allows us to extract specific parts of a date, such as the year, month, or day. To extract the day from a date, we can specify the day subfield within the extract() function.

Here’s an example:

SELECT extract('day' FROM DATE '2025-10-05') AS day;

Result:

+-----+
| day |
+-----+
| 5   |
+-----+

The day can also be specified as dayofmonth, which is the same as day. It’s also possible to specify the day of the week, or the day of the year:

SELECT 
    extract('dayofmonth' FROM DATE '2025-10-05') AS dayofmonth,
    extract('dayofweek' FROM DATE '2025-10-05') AS dayofweek,
    extract('dayofyear' FROM DATE '2025-10-05') AS dayofyear;

Result:

+------------+-----------+-----------+
| dayofmonth | dayofweek | dayofyear |
+------------+-----------+-----------+
| 5          | 0         | 278       |
+------------+-----------+-----------+

2. Using the date_part() Function

The date_part() function is similar to extract() and is another good option for extracting the day from a date. It takes two arguments: the part of the date we want to extract, and the date itself.

Here’s how we can use it:

SELECT 
    date_part('day', DATE '2025-10-05') AS day,
    date_part('dayofmonth', DATE '2025-10-05') AS dayofmonth,
    date_part('dayofweek', DATE '2025-10-05') AS dayofweek,
    date_part('dayofyear', DATE '2025-10-05') AS dayofyear;

Result:

+-----+------------+-----------+-----------+
| day | dayofmonth | dayofweek | dayofyear |
+-----+------------+-----------+-----------+
| 5   | 5          | 0         | 278       |
+-----+------------+-----------+-----------+

3. Using the day() Function

For a more concise approach, DuckDB provides the day() function, which is specifically designed to extract the day from a date. This function is a shorthand for extract('day' FROM date) and is very easy to use.

Here’s an example:

SELECT day(DATE '2025-10-05') AS day;

Result:

+-----+
| day |
+-----+
| 5   |
+-----+

4. Using the dayofmonth() Function

The dayofmonth() function is a synonym for day(), providing an alternative syntax for the same operation.

Example:

SELECT dayofmonth(DATE '2025-10-05') AS day;

Result:

+-----+
| day |
+-----+
| 5   |
+-----+

While day() is more concise, dayofmonth() is more explicit, and there should be no doubt as to which day it refers to.

5. Using the dayofweek() Function

The dayofweek() function extracts the day of the week from a date, returning an integer that represents the weekday. In DuckDB, the week starts with Sunday as 0 and ends with Saturday as 6.

Example:

SELECT dayofweek(DATE '2025-10-05') AS day_of_week;

Result:

+-------------+
| day_of_week |
+-------------+
| 0           |
+-------------+

6. Using the dayofyear() Function

The dayofyear() function extracts the day of the year from a date, returning an integer between 1 and 366 (for leap years).

Example:

SELECT dayofyear(DATE '2025-10-05') AS day_of_year;

Result:

+-------------+
| day_of_year |
+-------------+
| 278         |
+-------------+

This function can be helpful for time-series analysis or when working with yearly data cycles.

7. Using the strftime() Function

The strftime() function is typically used for formatting dates as strings, but it can also be used to extract specific parts of a date, such as the day. By using the '%d' format specifier, we can extract the day as a two-digit zero-padded string. For no padding, we can use the '%-d' format specifier.

Example:

SELECT 
    strftime(DATE '2025-10-05', '%d') AS zero_padded,
    strftime(DATE '2025-10-05', '%-d') AS not_padded;

Result:

+-------------+------------+
| zero_padded | not_padded |
+-------------+------------+
| 05          | 5          |
+-------------+------------+

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