What python object-type is compatible with the ms-access date/time field?

Question

I have a piece of code that tries to insert a datetime.datetime into an ms-access datebase's date/time (not extended date/time) field, yet I keep getting the data type mismatch error message. So my question is, which type do I use for this?

I tried using this code

    print(type(timeCET))
    >>>
    datetime.datetime

and

    crsr.execute("insert into WeatherAnalisis(Time_UTC) values ('timeCET.timedate')")
    cnxn.commit()
    >>>>
    pyodbc.DataError: ('22018', '[22018] [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. (-3030) (SQLExecDirectW)')

But it doesn't seem to work

Answer 1

You directly included the name of your datetime object as a quoted literal in SQL statement. As a result, Access throws an error since your are trying to insert a literal string value 'timeCET.timedate' (the name and not value of your actual datetime variable) into datetime column.

Instead, consider SQL parameterization. (Do not concatenate as single quotes will not work to enclose dates in Access SQL).

# PREPARED STATEMENT WITH QMARK
sql = "insert into WeatherAnalisis (Time_UTC) values (?)"

# EXECUTE QUERY WITH BINDED PARAM
crsr.execute(sql, timeCET)
cnxn.commit()