ValueError when reading dates from hfsql database using ODBC

With some struggles I connected to a hfsql server using ODBC. I’ve tried both pypyodbc and pyodbc. 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: ''

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

All rows contain a valid date.

This is the code that currently produces the error above:

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

 
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)

 
    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}})

 
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,

Answer

SELECT CAST(DeliveryDate AS varchar(12)) AS dd FROM Orders …

 
SELECT CAST(DeliveryDate AS varchar(12)) AS dd FROM Orders …​

(as suggested in a comment to the question) solved the issue.

Advertisement

Answer