apache/cassandra · error · ValueError

can't interpret %r as a date with format %s or as int

Error message

can't interpret %r as a date with format %s or as int

What it means

Date columns in COPY FROM accept either a date string matching the configured date format or (as a COPY TO fallback) a millisecond count from the epoch as an int. If the value matches neither, this ValueError is raised.

Source

Thrown at pylib/cqlshlib/copyutil.py:2032

        p = re.compile(r"(\d{4})-(\d{2})-(\d{2})\s?(?:'T')?"  # YYYY-MM-DD[( |'T')]
                       + r"(?:(\d{2}):(\d{2})(?::(\d{2})(?:\.(\d{1,6}))?))?"  # [HH:MM[:SS[.NNNNNN]]]
                       + r"(?:([+\-])(\d{2}):?(\d{2}))?")  # [(+|-)HH[:]MM]]

        def convert_datetime(val, **_):
            try:
                dtval = datetime.datetime.strptime(val, self.date_time_format)
                return dtval.timestamp() * 1000
            except ValueError:
                pass  # if it's not in the default format we try CQL formats

            m = p.match(val)
            if not m:
                try:
                    # in case of overflow COPY TO prints dates as milliseconds from the epoch, see
                    # deserialize_date_fallback_int in cqlsh.py
                    return int(val)
                except ValueError:
                    raise ValueError("can't interpret %r as a date with format %s or as int" % (val,
                                                                                                self.date_time_format))

            # https://docs.python.org/3/library/time.html#time.struct_time
            tval = time.struct_time((int(m.group(1)), int(m.group(2)), int(m.group(3)),  # year, month, day
                                    int(m.group(4)) if m.group(4) else 0,  # hour
                                    int(m.group(5)) if m.group(5) else 0,  # minute
                                    int(m.group(6)) if m.group(6) else 0,  # second
                                    0, 1, -1))  # day of week, day of year, dst-flag

            # convert sub-seconds (a number between 1 and 6 digits) to milliseconds
            milliseconds = 0 if not m.group(7) else int(m.group(7)) * pow(10, 3 - len(m.group(7)))

            if m.group(8):
                offset = (int(m.group(9)) * 3600 + int(m.group(10)) * 60) * int(m.group(8) + '1')
            else:
                offset = -time.timezone

            # scale seconds to millis for the raw value

View on GitHub (pinned to 88fd0f6a0e)

Solutions

  1. Reformat the date in the CSV to match the configured date_time_format (default ISO '%Y-%m-%d')
  2. Pass WITH DATEFORMAT='<pattern matching your csv>' to the COPY FROM statement
  3. Convert the date to milliseconds-from-epoch as an integer alternative

Example fix

// before
1,01/02/2026
// after
COPY t FROM 'f.csv' WITH DATEFORMAT='%m/%d/%Y';
// or reformat the cell to 2026-01-02
Defensive patterns

Strategy: validation

Validate before calling

import time
fmt = '%Y-%m-%d'
for row in rows:
    v = row['d']
    if not v.isdigit():
        try:
            time.strptime(v, fmt)
        except ValueError:
            raise ValueError(f"bad date {v!r}, expected {fmt} or epoch millis")

Try / catch

try:
    run_copy_from(...)
except ValueError as e:
    print(f"Date parse failed: {e}")  # reformat or set DATEFORMAT

Prevention

When it happens

Trigger: COPY FROM a CSV cell for a date column whose text doesn't match the DATEFORMAT/timestamp pattern and also isn't parseable as an integer — e.g. '01/02/2026' when the format expects '%Y-%m-%d', or a locale-formatted date.

Common situations: Exports from tools using a different date convention (US vs ISO), COPY TO round-trips where dates were printed as epoch millis and hand-edited, mismatch between the WITH DATEFORMAT option and the actual CSV contents.

Related errors

AI-assisted analysis of apache/cassandra@88fd0f6a0e (2026-09-10). Data as JSON: /api/errors/6d15caa110b8c928. Report an issue: GitHub.