pandas: BUG: pandas.to_datetime() does not respect exact format string with ISO8601

Just adding my two cents because I was running into the same exact problem. My use case is that I have a program that uses read_csv to load a csv file where the index is either just a date, or date and time. If it has a time I want to make it UTC time zone, if not then no time zone. The pythonic way to do it I thought would be try to read_csv with a parser using to_datetime that would try to match one format, and if that raised an exception try the other format. But by not raising the exception if there is not an exact match that approach does not work.

I would be in favor of an exact=‘strict’ or similar to truly enforce the exact match.

We’re having the same problem. I’d like to be able to add some argument(s) to the following code to make it either raise or return NaT:

> to_datetime(0.0, format="%Y-%m-%d", exact=True)
Timestamp('1970-01-01 00:00:00')

I like the sound of exact="strict".

So we tend to parse more easily lots of formats. I still don’t understand why this is a problem. It parses correctly as it should, even though you gave an underspecified format.

That’s exactly my problem. I have a very specific use case which requires to raise an exception when the date format doesn’t match. Trying '%Y-%m-%d' and '%Y-%m-%d %H:%M:%S' both won’t raise an exception for parsing '2012-01-01 10:00', so the application is unable to detect the proper (=exact, =non underspecified, =non overspecified) date format (that’s all I care about).

I tried to make this more strict through the parameters I quoted a number of times already, but this didn’t make the date conversion more strict. I don’t know how else I can make this function more strict to only accept date formats that I specify and raise an exception if they don’t.

In the iso-format case, the date format doesn’t need to be exact, but for all other formats an exception will be raised when the format doesn’t match. That’s what I find confusing.

We have also been bitten by this. Not only does it match a longer date format, but it parses integer types too. For instance:

 pd.to_datetime([1], format="%Y-%m-%d %H:%M")

doesn’t raise any errors and parses the date to be 1970-01-01 00:00:00.000000001 however, by just changing the separator it works in a very different (and, imho, less surprising) way:

 pd.to_datetime([1], format="%Y|%m|%d %H:%M")

raises ValueError: time data '1' does not match format '%Y|%m|%d %H:%M' (match).

I agree with @brandon-leapyear, a new flag should be created to enable this behaviour and format should always match exactly.

✨ This is an old work account. Please reference @brandonchinn178 for all future communication ✨

---

We’re also coming up against this. As a minimal example, this is very confusing behavior for me:

>>> pd.to_datetime('2022/02/01', format="%Y-%m-%d")
Timestamp('2022-02-01 00:00:00')
>>> pd.to_datetime('2022/02/01', format="%Y-%m")
Timestamp('2022-02-01 00:00:00')

We’re trying to require the input to EXACTLY be %Y-%m-%d; all other formats should error. The logic mentioned earlier in this thread is very odd behavior: if the string passed to format is a prefix of any ISO formats, format is implicitly converted to allow any ISO string. IMO this “any ISO format” logic should be under a completely different flag, like iso_format=True, and format should be an exact match

https://github.com/pandas-dev/pandas/blob/3cf30739dbd1162c9c81728ed2f8da949b6e367a/pandas/_libs/tslibs/parsing.pyx#L856

EDIT: as a hacky workaround, you can do:

# some string of characters unlikely to be in the column
# if they are in the column, the error message could get messed up;
# you might want to explicitly parse the error message with regex
PREFIX = "!&*@&!#"

try:
    pd.to_datetime(PREFIX + df["col"], format=PREFIX + "%Y-%m-%d")
except ValueError as e:
    # fix error message to remove the prefix
    raise ValueError(e.args[0].replace(PREFIX, ""))

this breaks a lot of tests for us as well. Very similar use case as @rsheftel. The strict mode that raises exceptions would work for us too.

The problem was that it would parse dates even though they were not compliant with the format string without raising an exception by using some “auto-magic” behaviour

Currently, it only does that if the format is ISO8601. So yes, this has everything to with ISO8601

For example, the following raises (format isn’t ISO8601)

pd.to_datetime('01-01-2000 00:00:00', format='%d-%m-%Y')

whereas the following doesn’t (format is ISO8601):

pd.to_datetime('2000-01-01 00:00:00', format='%Y-%m-%d')

I agree that to a user, this shouldn’t make a difference. So the issue is about fixing the ISO8601 fast-path so that it also respects exact

The problem is this part in pd.to_datetime():

       if format is not None:
            # There is a special fast-path for iso8601 formatted
            # datetime strings, so in those cases don't use the inferred
            # format because this path makes process slower in this
            # special case
            format_is_iso8601 = _format_is_iso(format)
            print 'format_is_iso8601', format_is_iso8601
            if format_is_iso8601:
                require_iso8601 = not infer_datetime_format
                format = None

When I change the condition to if format is not None and infer_datetime_format, it works as expected. Is this something that could be changed in Pandas. I find it quite confusing that despite explicitly setting infer_datetime_format=False there’s still some internal magic happening in this function. Others may feel the same, not sure…