1

I have several DataFrames that have Order_date column. In all DataFrames, the Order_date columns' dtypes are datetime64[ns]. Please note that these columns are filled with valid dates and NaT values.

An example of how Order_date column looks like when printed:

   Order_date

1 2017-01-01 2 NaT 3 NaT 4 NaT 5 2017-10-22

I am trying to populate these columns of DataFrames into MS Access table via pyodbc.

I get the following error:

pyodbc.DataError: ('22008', '[22008] [Microsoft][ODBC Microsoft Access Driver]Datetime field overflow (SQLExecDirectW)')

After research, I noticed that the Date/Time datatype for MS Access 2016 corresponds with ODBC datatype SQL_TIMESTAMP.

So, I tried the following to convert datetime64[ns] to SQL_TIMESTAMP:

import datetime
cursor.execute("SQL statement...VALUES(?)", datetime.datetime(order_date))

However, I get this error: TypeError: an integer is required (got type Timestamp).

What can I do to successfully populate Pandas/Numpy datetime64[ns] values into MS Access table? How can I convert datetime64[ns] values into SQL_TIMESTAMP?

CPU
  • 267
  • 1
  • 6
  • 16
  • same question ah?https://stackoverflow.com/questions/47666800/how-to-convert-pandas-nan-to-nat/47666975#47666975 – BENY Dec 06 '17 at 15:20

1 Answers1

0

IIUC - you were almost there. You just have to assign a Series returned by pd.to_datetime back to column:

df['Order_date'] = pd.to_datetime(df['Order_date'], errors='coerce')
MaxU - stand with Ukraine
  • 205,989
  • 36
  • 386
  • 419
  • Thank you. Your solution converts the columns into NaT. I have another question: do you know how I can convert Pandas datetime64[ns] values into MS Access SQL_TIMESTAMP values? Please note that I updated my question above. – CPU Dec 06 '17 at 16:21
  • @Mook, when you update a question you make a given answers invalid. Please ask a new question instead and consider accepting an answer... – MaxU - stand with Ukraine Dec 06 '17 at 23:17
  • @ MaxU - I accepted your answer. Thank you once again for your answer. – CPU Dec 06 '17 at 23:28