Python convert MS Access "Date/Time Extended" / bytestring to readable string or datetime object

98 Views Asked by At

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".

0

There are 0 best solutions below