If you’re getting an error that reads “Error: Month out of range, expected a value between 1 and 12” when using the strptime() function in DuckDB, it could be that you’re getting your months mixed up in your format string.

The error occurs when we specify a month that’s outside the range of 1 and 12. A date can only have months that are between 1 and 12, and so if we specify a number outside that range, then we’ll get the error. It’s possible that the date is for a country that uses a different date format to the one you’re specifying in your format string.

To fix this issue, be sure to use a valid date and that the format specifier is the correct one for the date/s being used. Another way to deal with the issue is to use the try_strptime() function.

Example of Error

Here’s an example of code that produces the error:

SELECT strptime('08/27/2035', '%d/%m/%Y');

Output:

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

The error is quite self-explanatory, but it’s essentially telling us that the date uses a month that’s outside the allowed range of 1 and 12.

Solution 1

To fix this issue, we must be sure to use the correct format string for the date/s being provided. We must also be sure that the date itself is valid.

In my case, I specified a date of 08/27/2035, which implies that it’s mm/dd/yyyy. But my format string (%d/%m/%Y) implies that it’s dd/mm/yyyy.

So I have choice; change the date or change the format string.

Here’s an example of changing the format string:

SELECT strptime('08/27/2035', '%m/%d/%Y');

Result:

+------------------------------------+
| strptime('08/27/2035', '%m/%d/%Y') |
+------------------------------------+
| 2035-08-27 00:00:00                |
+------------------------------------+

This time it worked fine. Here, I simply switched the position of the %m and %d format specifiers.

The other option is to change the date:

SELECT strptime('27/08/2035', '%d/%m/%Y');

Output:

+------------------------------------+
| strptime('27/08/2035', '%d/%m/%Y') |
+------------------------------------+
| 2035-08-27 00:00:00                |
+------------------------------------+

This also resolved the issue.

In both cases we simply made sure that the format string provided by the second argument aligned with the format that the date was provided in.

Solution 2

Another way to deal with the issue is to use the try_strptime() function. Instead of returning an error message, this function returns NULL on failure.

.nullvalue 'null'
SELECT try_strptime('08/27/2035', '%d/%m/%Y');

Output:

+----------------------------------------+
| try_strptime('08/27/2035', '%d/%m/%Y') |
+----------------------------------------+
| null                                   |
+----------------------------------------+

Here, I used .nullvalue 'null' on the first line so that DuckDB would return the string null instead of an empty string (which is the default value for null). I did this merely for the benefit of this example so that we could see that the function indeed returned null.

Caution

It’s possible to run strptime() without error, even when the format string is the wrong way around. This can happen when the day part is 12 or less.

For example, consider the following:

SELECT 
    strptime('03/04/2035', '%d/%m/%Y') AS '%d/%m/%Y',
    strptime('03/04/2035', '%m/%d/%Y') AS '%m/%d/%Y';

Output:

+---------------------+---------------------+
|      %d/%m/%Y       |      %m/%d/%Y       |
+---------------------+---------------------+
| 2035-04-03 00:00:00 | 2035-03-04 00:00:00 |
+---------------------+---------------------+

Both of these ran without error. The problem is that they returned different results. One resulted in the 3rd of April, while the other result in the 4th of March.

When we run this function against a table that contains many dates, it’s possible that we could be getting the wrong outcome, even though we’re not getting an error. This would happen if none of the dates contain a day greater than 12. So it pays to double-check that you’ve got the format string the right way around, even if you aren’t seeing an error.