When working with DuckDB, sometimes we might need to get the weekday name from a date or timestamp value. And sometimes we might want just the abbreviated weekday name, rather than the full name. For example, we might want Mon instead of Monday.

Fortunately, DuckDB provides us with the strftime() function, which will enable us to do just that; to get the shortened/abbreviated day name from a given date or timestamp.

Example

Here’s how to use strftime() to get the abbreviated day name from a date value in DuckDB:

SELECT strftime(DATE '2025-12-05', '%a') AS abbreviated_dayname;

Result:

+---------------------+
| abbreviated_dayname |
+---------------------+
| Fri                 |
+---------------------+

When we use the strftime() function, we pass the date as the first argument and the format string as the second argument. The format string can consist of one or more format specifiers. In this case, our format string only consisted of one format specifier, because we only wanted one date part to be returned (i.e. the abbreviated weekday name). To achieve this, we used the %a format specifier, because that’s the format specifier that returns the abbreviated day name.

Here are more abbreviated day names:

SELECT 
    strftime(DATE '2025-01-05', '%a') AS Sunday,
    strftime(DATE '2025-01-06', '%a') AS Monday,
    strftime(DATE '2025-01-07', '%a') AS Tuesday,
    strftime(DATE '2025-01-08', '%a') AS Wednesday,
    strftime(DATE '2025-01-09', '%a') AS Thursday,
    strftime(DATE '2025-01-10', '%a') AS Friday,
    strftime(DATE '2025-01-11', '%a') AS Saturday;

Result:

+--------+--------+---------+-----------+----------+--------+----------+
| Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday |
+--------+--------+---------+-----------+----------+--------+----------+
| Sun    | Mon    | Tue     | Wed       | Thu      | Fri    | Sat      |
+--------+--------+---------+-----------+----------+--------+----------+

We can switch the format specifier to uppercase if we wanted to get the full day name:

SELECT strftime(DATE '2025-01-05', '%A') AS long_dayname;

Result:

+--------------+
| long_dayname |
+--------------+
| Sunday       |
+--------------+

We can also use other format specifiers to get other date parts. Here’s a full list of format specifiers that can be used with the strftime() function.