Formatting Dates with STRFTIME() in DuckDB

In DuckDB, the strftime() function is a handy tool for formatting date and timestamp values as strings. It accepts the date/timestamp value and a format string as arguments. The function then returns the date/time in the format provided by the format string.

Syntax

The strftime() function converts date and timestamp values into formatted strings. The basic syntax is:

strftime(timestamp, format_string)

Where:

timestamp is the date/timestamp value you want to format.
format_string is a string containing formatting specifiers.

Basic Example

Here’s an example to demonstrate:

SELECT strftime(DATE '2026-12-07', '%a, %d %b %y') AS formatted_date;

Result:

+----------------+
| formatted_date |
+----------------+
| Mon, 07 Dec 26 |
+----------------+

This format was determined by the format string (second argument). In this case the format string is '%a, %d %b %y'. This particular format string contains four format specifiers. Each of these represents a date component from the date in the first argument. These are kind of like placeholders for each date component, and each format specifier outputs its component in its own way.

To further illustrate this, let’s remove all but one of the format specifiers:

SELECT strftime(DATE '2026-12-07', '%a') AS formatted_day;

Result:

+---------------+
| formatted_day |
+---------------+
| Mon           |
+---------------+

The %a format specifier represents the abbreviated weekday name, and we can see how this fits in with the previous example.

Format Specifiers

There’s a pretty good selection of format specifiers that we can use with the strftime() function. Each format specifier begins with a percent sign (%).

Here are some of the more commonly used format specifiers:

Specifier	Description	Example
%Y	Four-digit year	2023
%m	Month as two digits (01-12)	05
%d	Day of month as two digits (01-31)	14
%H	Hour in 24-hour format (00-23)	13
%M	Minute as two digits (00-59)	30
%S	Second as two digits (00-59)	45
%w	Weekday as a number (0-6, Sunday is 0)	2
%j	Day of year (001-366)	142
%W	Week of year (00-53)	23

Here’s a full list of format specifiers that you can use for reference.

Some More Date Format Examples

Here’s the same date displayed in various formats:

SELECT 
    strftime(DATE '2026-12-07', '%a, %d %b %y') AS '%a, %d %b %y',
    strftime(DATE '2026-12-07', '%A, %-d %B %Y') AS '%A, %-d %B %Y',
    strftime(DATE '2026-12-07', '%d/%m/%Y') AS '%d/%m/%Y',
    strftime(DATE '2026-12-07', '%m/%d/%Y') AS '%m/%d/%Y';

Result:

+----------------+-------------------------+------------+------------+
|  %a, %d %b %y  |      %A, %-d %B %Y      |  %d/%m/%Y  |  %m/%d/%Y  |
+----------------+-------------------------+------------+------------+
| Mon, 07 Dec 26 | Monday, 7 December 2026 | 07/12/2026 | 12/07/2026 |
+----------------+-------------------------+------------+------------+

Timestamp Example

We can see by the list of format specifiers above that we can also format the time component of timestamp values. Here’s an example of doing that:

SELECT strftime(TIMESTAMP '2027-03-09 18:15:46', '%d/%m/%Y %H:%M:%S %p') AS formatted_timestamp;

Result:

+------------------------+
|  formatted_timestamp   |
+------------------------+
| 09/03/2027 18:15:46 PM |
+------------------------+

When using strftime(), if we want a date/time component to be displayed we must provide a format specifier for that component. Therefore, we can’t simply omit the whole date component or time component if we want it to appear in its original format. We still need to specify a format for each component.

Here’s an example of what I mean:

SELECT strftime(TIMESTAMP '2027-03-09 18:15:46', '%d/%m/%Y') AS formatted_timestamp;

Result:

+---------------------+
| formatted_timestamp |
+---------------------+
| 09/03/2027          |
+---------------------+

This is the same example as before, except in this example I deleted the format string for the time component. In other words, I deleted %H:%M:%S %p. The result of this is that only the date portion is displayed when I run the query.

Conversely, if we have a DATE value (i.e. with no time component), and we include format specifiers for time components, we still get those time components in the result:

SELECT strftime(DATE '2027-03-09', '%d/%m/%Y %H:%M:%S %p') AS formatted_date;

Result:

+------------------------+
|     formatted_date     |
+------------------------+
| 09/03/2027 00:00:00 AM |
+------------------------+

With Date Calculations

We can include date calculations when using strftime(). For example:

SELECT 
    strftime(current_date, '%A, %-d %B') AS 'Today',
    strftime(date_add(current_date, INTERVAL 7 DAY), '%A, %-d %B') AS '7 days from today';

Output:

+-----------------+-------------------+
|      Today      | 7 days from today |
+-----------------+-------------------+
| Sunday, 2 March | Sunday, 9 March   |
+-----------------+-------------------+

String Literals in the Format String

You may have noticed that the above examples included some string literals. For example, the forward slash sign (/) is a string literal. We can include pretty much whatever string literal we want in the format string, and it will appear exactly as specified.

Therefore, we can do stuff like this:

SELECT strftime(DATE '2026-12-11', 'Today is %A... YAY!!!') AS formatted_date;

Result:

+---------------------------+
|      formatted_date       |
+---------------------------+
| Today is Friday... YAY!!! |
+---------------------------+

Escaping the Percent Sign

If you need to include a percent sign in the output, you can escape it with another percent sign.

Example:

SELECT strftime(DATE '2026-12-11', 'I''m 100%% sure that today is %A') AS escaped_output;

Result:

+------------------------------------+
|           escaped_output           |
+------------------------------------+
| I'm 100% sure that today is Friday |
+------------------------------------+

Here’s what happens if we only provide a single percentage sign:

SELECT strftime(DATE '2026-12-11', 'I''m 100% sure that today is %A') AS escaped_output;

Result:

Invalid Input Error: Failed to parse format specifier I'm 100% sure that today is %A: Unrecognized format for strftime/strptime: % 
LINE 1: SELECT strftime(DATE '2026-12-11', 'I''m 100% sure that today is %A') AS e...
                                           ^

In these examples I also escaped the apostrophe with another apostrophe. I did this in order to prevent the apostrophe inadvertently terminating the format string.