5 Functions that Extract the Month from a Date in DuckDB

by

Sometimes when working with SQL databases like DuckDB, we need to return the month part from a date for presentation or for further processing. DuckDB provides us with a handful of functions that can help in this regard.

In this article, we’ll look at five different functions we can use to extract the month from a date in DuckDB.

1. The month() Function

DuckDB provides the month() function, which is specifically designed to extract the month from a date.

Here’s an example:

SELECT month(DATE '2025-08-05') AS month;

Result:

+-------+
| month |
+-------+
| 8     |
+-------+

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 month, we can specify the month subfield within the extract() function.

Here’s an example:

SELECT extract('month' FROM DATE '2025-08-05') AS month;

Result:

+-------+
| month |
+-------+
| 8     |
+-------+

3. The date_part() Function

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

Example:

SELECT date_part('month', DATE '2025-08-05') AS month;

Result:

+-------+
| month |
+-------+
| 8     |
+-------+

4. The monthname() Function

If you need the actual month name instead of its number, the monthname() function could be what you’re looking for. This function returns the full month name from the date, in English.

Example:

SELECT monthname(DATE '2025-08-05') AS full_monthname;

Result:

+----------------+
| full_monthname |
+----------------+
| August         |
+----------------+

5. 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, such as the month or month name as a formatted string. We can use the '%m' format specifier to get the zero-padded month, or we can use '%-m' to remove the zero-padding. We can also get the full month name or the shortened month name with %B and %b respectively.

Here’s an example that demonstrates these options:

SELECT 
    strftime(DATE '2025-08-05', '%m') AS zero_padded,
    strftime(DATE '2025-08-05', '%-m') AS not_padded,
    strftime(DATE '2025-08-05', '%B') AS full_monthname,
    strftime(DATE '2025-08-05', '%b') AS short_monthname;

Result:

+-------------+------------+----------------+-----------------+
| zero_padded | not_padded | full_monthname | short_monthname |
+-------------+------------+----------------+-----------------+
| 08          | 8          | August         | Aug             |
+-------------+------------+----------------+-----------------+

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