If you’ve ever used the strptime() function to create a timestamp in DuckDB, you may be aware that it will return an error if it can’t construct the timestamp from the format string/s provided.

While such an error could be useful in some situations, it could also be annoying in others.

Fortunately, DuckDB also provides the try_strptime() function, which will suppress any error that we might ordinarily get in such cases. This function returns null instead of an error.

Syntax

The syntax for try_strptime() is basically the same as for strptime(). It can be used in two ways:

try_strptime(text, format)
try_strptime(text, format-list)

Where:

• 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. The format string uses format specifiers to define the structure of the date and time components in the input string.
• format-list: This is a list of format strings. DuckDB will use the first one that succeeds.

The function will convert the text value to a valid TIMESTAMP value by using the format string as guidance. If none of the format strings succeed, then null is returned.

Example

Here’s an example of using the try_strptime() function:

.nullvalue 'null'
SELECT try_strptime('20/07/2023', '%m/%d/%Y');

Output:

+----------------------------------------+
| try_strptime('20/07/2023', '%m/%d/%Y') |
+----------------------------------------+
| null                                   |
+----------------------------------------+

Here, my format string indicates that the 20 part of the date is the month. There are only 12 months in a year, and so try_strptime() couldn’t convert the string to a valid date. As a result, it returned null.

In this example I also used .nullvalue 'null' in the DuckDB CLI in order to have NULL values appear as the string null, instead of the default empty string.

In Contrast: The strptime() Function

Here’s what happens if we use strptime() instead of try_strptime():

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 function returns an error instead of a NULL value. One benefit of this is that the error message tells us what caused the error, which provides a clue as to how to fix it.

However, if you prefer to have a NULL value returned instead, then try_strptime() is the function you need.

Using a Format List

As with the strptime() function, try_strptime() accepts a list of format specifiers. The function will use the first one that succeeds.

Here’s an example:

SELECT try_strptime('15/07/2023', ['%m/%d/%Y','%d/%m/%Y']);

Output:

+---------------------------------------------------------------------+
| try_strptime('15/07/2023', main.list_value('%m/%d/%Y', '%d/%m/%Y')) |
+---------------------------------------------------------------------+
| 2023-07-15 00:00:00                                                 |
+---------------------------------------------------------------------+

Now that we’ve added another format string, the function used that one, instead of returning null like in the earlier example.

However, if the second format string fails, then we’ll still get null:

SELECT try_strptime('15/07/2023', ['%m/%d/%Y','%d-%m-%Y']);

Output:

+---------------------------------------------------------------------+
| try_strptime('15/07/2023', main.list_value('%m/%d/%Y', '%d-%m-%Y')) |
+---------------------------------------------------------------------+
| null                                                                |
+---------------------------------------------------------------------+

The strptime() function also accepts a format list, and so there’s no difference between the two functions in that regard. The main difference is in how they behave when an operation fails; strptime() returns an error, whereas try_strptime() returns null.