Home > OS >  pypyodbc: Valueerror when reading dates from hfsql database (using pandas?)
pypyodbc: Valueerror when reading dates from hfsql database (using pandas?)

Time:06-30

With some strugles I connected to a hfsql server using pypyodbc. My goal is to get some insights in the data (and visualize some aspects).

For some planning visualization I need to read out some of the data, which works fine for most tables and columns. However, when I try to read out a column containing dates i get a ValueError:

invalid literal for int() with base 10: ''

All rows contain a valid date.

This is the code that currently produces this^ error:

deadlines = db.cursor()
query = ("SELECT DeliveryDate FROM Orders WHERE Finished = 0")
deadlines.execute(query)
print(deadlines.fetchone()) #<- this goes wrong

db is the database (it works with other queries)

    print(deadlines.description)

gives: [('deliverydate', <class 'datetime.date'>, 11, 9, 9, 0, True)]

I also tried:

pandas.read_sql(query,db,parse_dates={'DeliveryDate': {"dayfirst": True}})

(dates are e.g. 27-6-2022) Which unfortunately gives the same error.

Any help would be appreciated, cheers,

CodePudding user response:

SELECT CAST(DeliveryDate AS varchar(12)) AS dd FROM Orders … (as suggested) solved the issue.

  • Related