IBM DB2 CAST AS VARCHAR versus Python Pandas to_datetime Function

I have the line

CAST(SURGERY.DTM AS VARCHAR(30)) AS appt_dt

in a SQL file hitting an IBM DB2 database. For various reasons, I have to convert to VARCHAR, so leaving out the CAST is not an option. The problem is that this casting is choosing a very poor format. The result comes out like this: 2020-06-09-13.15.00.000000. We have the four-digit year with century, month, day of the month. So far, so good. But then there is the really bad decimal-separated 24-hour hour, minute, and then seconds with microseconds. My goal is to read these dates quickly into a pandas dataframe in Python, and I can’t get pandas to parse this kind of date, presumably because it grabs the 13.15 for the hour, 00.000000 for the minute, and then has nothing left over for the seconds. It errors out. My attempt at a parser was like this:

parser_ibm_db(date_str: str) -> pd.tslib.Timestamp:
    return pd.to_datetime(date_str, format='$Y-%m-%d-%H.%M.%S')

but it doesn’t work. Neither does the option infer_datetime_format or nothing at all.

So here is my question: is there a way either to control the formatting of the CAST function better, or is there a way to read the result into pandas? I’d be perfectly happy with either approach.

One idea I had with the second approach was to limit the %H and %M options somehow to look at only 2 characters, but I don’t know how to do that and the documentation doesn’t tell me how.

A brute force method would be to read the csv data in, search for these kinds of strings, and replace the first two periods with colons. The date parser would have no trouble with that. But that would involve an extra processing step that I’d rather avoid.

Thanks for your time!

Answer

Change your format string:

dt_string = '2020-06-09-13.15.00.000000'
pd.to_datetime(dt_string, format='%Y-%m-%d-%H.%M.%S.%f')

Correctly converts the string:

Timestamp('2020-06-09 13:15:00')

Advertisement

Answer