DateTime Overflow with Valid Date Access Insert

Question

To anyone that can help, thank you. I am getting a very strange error when running a INSERT statement using pyodbc. The error code is:

cursor.execute(QueryInsert,params)
pyodbc.DataError: ('22008', '[22008] [Microsoft][ODBC Microsoft Access 
Driver]Datetime field overflow  (36) (SQLExecDirectW)')

This happens consistently with the datetime 1986-03-28 00:00:00

The code I am using is:

###Necessary Imports
from fredapi import Fred
import pyodbc
import datetime

###Connect to Access Database
conn = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" 
+r"DBQ=G:\Financial Modelling\Lease Database v1.0.accdb;")
cursor = conn.cursor()

###3M Libor
SourceCode = 'GBP3MTD156N'
fred = Fred(api_key='insert-api-key')
data = fred.get_series_all_releases(SourceCode)
A = data.shape[0]

###Cycle Through Results
for i in range(1,A):
    date1 = data.loc[i,'date']
    print(date1)
###execute query at date and only upload if empty
existquery = "SELECT * FROM EconVars WHERE SourceCode = '" + SourceCode + "' 
AND ValueDate = " + \
            "#"+str(date1.month)+"/"+str(date1.day)+"/"+str(date1.year)+"#"
cursor.execute(existquery)
existData = cursor.fetchall()

###check if empty
if len(existData) == 0:
    value1 = data.loc[i,'value']
    Description = '3M Libor'
    Source1 = 'Fred'
    params = (date1,value1,Description,Source1,SourceCode)
    QueryInsert = """INSERT into EconVars (ValueDate, ReportedValue, 
Description, Source,SourceCode)
            Values(?,?,?,?,?)"""
    cursor.execute(QueryInsert,params)
    cursor.commit()

###Commit Cursor for 3M LIBOR    
cursor.commit()
cursor.close()

The table in the access file I am using has 5 columns ValueDate defined as Date/Time (Short Date) ReportedValue as Number (Double) Description as Short Text Source As Short Text SourceCode As Short Text

Has anyone seen this error before or able to replicate it?

Python 3.7.2 64bit pyodbc 4.0.25 W10 64bit and Office 365 64bit

Thank you to anyone who has any ideas.

Does `print(repr(date1))` show you `datetime.datetime(1986, 3, 28, 0, 0)` ? — Gord Thompson, Jan 17 '19 at 14:00

score 2 · Answer 1 · answered Jan 17 '19 at 14:12

2

I figured out the problem is not with the datetime column provided by the API.

It is actually that the reported value, which should be a double, when missing is actually 'NaT' which I believe is a numpy NULL value or something equivalent.

Access is expecting a double value.

answered Jan 17 '19 at 14:12

user78913

73
7

Strange. For a NULL in a numeric column I would have expected `NaN`, not `NaT`. Still, good that you got it sorted out. – Gord Thompson Jan 17 '19 at 14:33

score 0 · Answer 2 · answered Mar 21 '19 at 01:41

Here's an example of how to change your datetime field to what OP is saying in their answer that worked for me (in pandas):

import pandas as pd

df = pd.DataFrame(['01/01/2019',None], columns=['datetime_field'])
df['datetime_field'] = pd.to_datetime(df['datetime_field'])

df['datetime_field'] = pd.to_datetime(df['datetime_field'], errors='coerce').where(df['datetime_field'].notnull(), 0.0)

Originally the nulls in this field are NaT.

pandas where docs

DateTime Overflow with Valid Date Access Insert

2 Answers2