Get the Day Name from a Date in DuckDB

DuckDB provides us with a couple of ways to get the weekday name from a date or timestamp value. This could be the full day name, such as Monday, or the short/abbreviated day name, such as Mon.

Here are two ways to return the day name from a date in DuckDB.

The `dayname()` Function

DuckDB has a dayname() function that returns the day name (in English) from a given date or timestamp value:

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

Result:

+---------+
| dayname |
+---------+
| Tuesday |
+---------+

If all you want is the full day name, there’s probably no need to look any further than this function. But there is another function we can use. And it also allows us to get the abbreviated day name.

The `strftime()` Function

The strftime() function is typically used for formatting date and timestamps. However, the way it operates makes it perfect for returning just a single date part, such as the day name.

Not only can we get the day name, but we can also get the abbreviated day name:

SELECT 
    strftime(DATE '2025-08-05', '%A') AS full_dayname,
    strftime(DATE '2025-08-05', '%a') AS short_dayname;

Result:

+--------------+---------------+
| full_dayname | short_dayname |
+--------------+---------------+
| Tuesday      | Tue           |
+--------------+---------------+

So to get the full weekday name we use %A (uppercase A), and to get the short weekday name it’s %a (lowercase a). These are format specifiers. In this example we only wanted the day name and so we only needed to use one format specifier. We can also use multiple format specifiers in order to get multiple date parts in the output. Here’s a full list of format specifiers that can be used with the strftime() function.

The dayname() Function

The strftime() Function

The `dayname()` Function

The `strftime()` Function