Understanding the STRPTIME() Function in DuckDB

Posted on by Ian

DuckDB provides us with a good selection of date/time functions. The strptime() function is a useful one for times where you need to convert a date string into a valid timestamp value; its sole purpose is to parse strings into timestamps.

In this article, we’ll look at how the strptime() function works, along with some examples to demonstrate.

What is the strptime() Function?

The strptime() function in DuckDB is used to convert a string into a timestamp based on a specified format. The name strptime stands for “string parse time,” and it is commonly used in programming languages and databases to interpret date and time strings.

The way it works is that we can pass a string that contains a date in a non-standard format, and we specify how DuckDB should interpret the string when building the timestamp. DuckDB will go ahead and create the timestamp based on our input.

strptime() can be especially useful when dealing with data that comes in various text formats and needs to be standardized into a consistent datetime format for analysis or storage.

Syntax of strptime()

The basic syntax of the strptime() function in DuckDB is as follows:

strptime(string, format)
  • string: This is the input string that you want to convert into a timestamp. It should represent a date, time, or both in a recognizable format.
  • format: This is a format string that specifies how the input string should be interpreted. It uses format specifiers to define the structure of the date and time components in the input string.

Examples

Let’s look at some practical examples to understand how strptime() works in DuckDB.

Example 1: Parsing a Simple Date String

Suppose we have a string "Saturday, 20 December 2025" that we want to convert into a timestamp value. We can do the following:

SELECT strptime('Saturday, 20 December 2025', '%A, %-d %B %Y');

Output:

2025-12-20 00:00:00

We can see that DuckDB was able to parse the first argument as a timestamp value because our second argument specified how the first argument was formatted. It’s like we gave DuckDB a little nudge in the right direction. Once DuckDB knew the format of the first argument, it was able to parse it accordingly.

Our input string didn’t contain a time part and so strptime() returned 00:00:00 for that part.

Example 2: Parsing a Date and Time String

Let’s add a time part to the input string:

SELECT strptime('Saturday, 20 December 2025 - 08:32:45 PM', '%A, %-d %B %Y - %I:%M:%S %p');

Output:

2025-12-20 20:32:45

The time portion is included as specified.

Example 3: A Different Date Format

Here’s an example that uses a date string in MM/DD/YYYY format:

SELECT strptime('10/07/2023', '%m/%d/%Y');

Output:

2023-10-07 00:00:00

Converting the Result to a DATE Value

We can cast the output to a DATE value if we don’t want the time portion included in the result:

SELECT cast(strptime('10/07/2023', '%m/%d/%Y') AS DATE);

Output:

2023-10-07

Mismatched Format Specifiers

It’s important that the format string matches the structure of the input string. Using incorrect specifiers can lead to errors or incorrect parsing.

Here’s what happens when we change just one of the format specifiers from the previous example:

SELECT strptime('10/07/2023', '%B/%d/%Y');

Output:

Invalid Input Error: Could not parse string "10/07/2023" according to format specifier "%B/%d/%Y"
10/07/2023
^
Error: Expected a full month name (January, February, etc...)

In this case I swapped %m with %B (i.e. the previous example uses '%m/%d/%Y' whereas this example uses '%B/%d/%Y'). This one small change resulted in an error.

In this case the error occurred because %B is the format specifier for the full month name, but my date string didn’t include the full month name – it used the month number.

Invalid Dates

While the strptime() function can be really good at parsing our strings as a timestamp value, there’s only so much it can do. If we pass something that can’t be parsed, then we shouldn’t expect it to perform miracles.

For example, if we provide a date with an out of range month:

SELECT strptime('20/07/2023', '%m/%d/%Y');

Output:

Invalid Input Error: Could not parse string "20/07/2023" according to format specifier "%m/%d/%Y"
20/07/2023
^
Error: Month out of range, expected a value between 1 and 12

This error occurred because I specified a month of 20. There’s only 12 months in a year, and so strptime() returned an error.

If this happens, check that you haven’t got the format string around the wrong way. For example, maybe the 20 part is actually the day – not the month. In that case, the format string should be '%d/%m/%Y' instead of '%m/%d/%Y'.

Let’s try that:

SELECT strptime('20/07/2023', '%d/%m/%Y');

Output:

2023-07-20 00:00:00

This time it worked as expected.

Format Specifiers

As seen in the previous examples, the format parameter in strptime() uses specific format specifiers to parse the input string. These specifiers are placeholders that represent different components of a date or time. Here are some of the more commonly used format specifiers:

  • %Y: Four-digit year (e.g., 2023)
  • %y: Two-digit year (e.g., 23)
  • %m: Two-digit month (e.g., 01 for January)
  • %-m: Non-padded month (e.g., 1 for January)
  • %d: Two-digit day of the month (e.g., 07)
  • %H: Two-digit hour in 24-hour format (e.g., 14 for 2 PM)
  • %M: Two-digit minute (e.g., 05)
  • %S: Two-digit second (e.g., 09)
  • %j: Day of the year as a zero-padded decimal number (e.g., 01)
  • %-j: Day of the year as a (non-padded) decimal number (e.g., 1)
  • %A: Full weekday name (e.g., Monday)
  • %a: Abbreviated weekday name (e.g., Mon)
  • %B: Full month name (e.g., January)
  • %b: Abbreviated month name (e.g., Jan)

These specifiers allow us to define the exact structure of the input string so that strptime() can correctly interpret it.

Here’s a full list of DuckDB format specifiers if you’re interested.