Cannot insert datetime.date object into Sql Server

[FrankMillman]

Python: 3.7.2
pyodbc: 4.0.27
OS: Windows10 64 bit
DB: Sql Server
Driver: I installed ODBC Driver 17 for SQL Server - don't know how to check if it is using it
Connection string: driver='sql server', server=r'localhost\sqlexpress', database={db}, uid={uid}, pwd={pwd}, trusted_connection=True

As abbreviations in the following, dt refers to a python datetime.date object, dtm refers to a python datetime.datetime object.

If I create a column of type DATE and insert a dt, I get this error -

pyodbc.Error: ('HYC00', '[HYC00] [Microsoft][ODBC SQL Server Driver]Optional feature not implemented (0) (SQLBindParameter)')

If I create a column of type DATE and insert a dtm, it accepts it, but when I retrieve it I get a string 'yyyy-mm-dd'.

If I create a column of type DATETIME and insert a dt, I get the same error as above.

If I create a column of type DATETIME and insert a dtm, it accepts it and returns a dtm.

Ideally I would like to create a column of type DATE, insert a dt, and get back a dt.

Am I doing something wrong?

[gordthompson]

DRIVER=SQL Server in your connection string will use the very old (circa SQL Server 2000) ODBC driver that ships with Windows. If you want to use the newer driver that you installed then put

DRIVER=ODBC Driver 17 for SQL Server

in your connection string.

don't know how to check if it is using it

After you've established your connection (cnxn = pyodbc.connect(...)) you can do

print(f'driver: {cnxn.getinfo(pyodbc.SQL_DRIVER_NAME)}, version: {cnxn.getinfo(pyodbc.SQL_DRIVER_VER)}')
# driver: msodbcsql17.dll, version 17.04.0001

With the older driver you would see driver: SQLSRV32.DLL, version: 06.03.9600.

[FrankMillman]

Thank you so much - it works perfectly.

It took me two days to get it working. Eventually I figured out that the connection string parameter 'trusted_connection' now requires 'Yes' instead of True!