I have an MS Access table with a field which has the date type "Date/Time Extended". It holds the value "4:00:00.0000000 PM". I retrieve this using pyodbc (drivers: 'SQL Server', 'ODBC Driver 17 for SQL Server', 'Microsoft Access Driver (*.mdb, .accdb)', 'Microsoft Excel Driver (.xls, *.xlsx, *.xlsm, .xlsb)', 'Microsoft Access Text Driver (.txt, *.csv)'):
conn_str = r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=database.accdb'
conn = pyodbc.connect(conn_str)
sql = "SELECT TradeTime FROM TradeTable"
with conn:
cursor = conn.cursor()
cursor.execute(sql)
columns = [column[0] for column in cursor.description]
df = [tuple(row) for row in cursor.fetchall()]
df = pd.DataFrame(df, columns=columns)
return df
When I retrieve this, I obtain a bytes object in the returned DataFrame looking like this: b'0000000000000693593:0000000576000000000:7\x00'.
How can I convert this back to a string, like "4:00:00.0000000 PM" or datetime object, so that I can work with it?
I tried .decode() and the struct library but didn't obtain any good results. All I know is that according to the documentary the datetime is an "Encoded string of 42 bytes".