When working with dates in DuckDB, some common tasks we might need to perform include extracting date parts from a date or timestamp value. For example we might want to extract the year from a date. Fortunately, DuckDB provides us with an abundance of options for doing that.

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

1. The year() Function

Perhaps the most obvious place to start is with the year() function. As its name suggests, this function returns the year from a date.

Here’s an example:

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

Result:

+------+
| year |
+------+
| 2025 |
+------+

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 year, use the year subfield:

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

Result:

+------+
| year |
+------+
| 2025 |
+------+

There’s also an isoyear subfield that can be used to extract the ISO Year number (starts on Monday of week containing Jan 4th). This means that there are times where a different year could be returned than you might expect.

Here’s an example of what I mean:

SELECT 
    extract('isoyear' FROM DATE '2025-12-31') AS '2025',
    extract('isoyear' FROM DATE '2026-12-31') AS '2026',
    extract('isoyear' FROM DATE '2027-12-31') AS '2027',
    extract('isoyear' FROM DATE '2028-12-31') AS '2028';

Result:

+------+------+------+------+
| 2025 | 2026 | 2027 | 2028 |
+------+------+------+------+
| 2026 | 2026 | 2027 | 2028 |
+------+------+------+------+

We can see that the first column’s ISO year is 2026, even though the year on the date is actually 2025. However, with the following years, the ISO year aligns perfectly with the year on the actual date.

3. The date_part() Function

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

Example:

SELECT 
    date_part('year', DATE '2025-12-31') AS year,
    date_part('isoyear', DATE '2025-12-31') AS iso_year;

Result:

+------+----------+
| year | iso_year |
+------+----------+
| 2025 | 2026     |
+------+----------+

This function also accepts isoyear as its first argument, and so we can get the ISO year in addition to extracting the year from the date part.

4. The isoyear() Function

DuckDB also has a function called isoyear(), which we can use specifically for getting the ISO year.

Example:

SELECT isoyear(DATE '2025-12-31') AS iso_year;

Result:

+----------+
| iso_year |
+----------+
| 2026     |
+----------+

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 as a formatted string, including the year.

Actually, the year itself can be returned in various forms, depending on the format specifier that we use. This makes the strftime() function quite a flexible function for returning the year (or other date parts) in different formats.

Here’s are examples:

SELECT 
    strftime(DATE '2007-12-31', '%Y') AS with_century,
    strftime(DATE '2007-12-31', '%-y') AS without_century,
    strftime(DATE '2007-12-31', '%y') AS without_century_padded,
    strftime(DATE '2007-12-31', '%G') AS iso_year;

Result:

+--------------+-----------------+------------------------+----------+
| with_century | without_century | without_century_padded | iso_year |
+--------------+-----------------+------------------------+----------+
| 2007         | 7               | 07                     | 2008     |
+--------------+-----------------+------------------------+----------+

Here, we used four different format specifiers to return the year in four different formats. Here’s a full list of format specifiers that can be used with this function.