CODEWITHSUNDEEP
pythonsqldatetimepyodbc
Sameer Patel
Sameer Patel

Reputation: 977

Python / pyodbc - WHERE statement to filter dates?

I have used pydoc to fetch data from sql database.

I would like to use a WHERE statement to filter the dates. I have:

cursor.execute("SELECT isnull(SOP30200.SOPNUMBE,''), isnull(SOP30200.docdate,'') from SOP30200 where SOP30200.docdate > datetime.datetime(2015,1,1,0,0)")

and I get the error:

ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find either column "datetime" or the user-defined function or aggregate "datetime.datetime", or the name is ambiguous. (4121) (SQLExecDirectW)')

Without the WHERE statement I successfully get the data. I did check the type of the "docdate" field that is pulled out and it is datetime.datetime.

EDIT: should also point out that the date that is extracted is in the form datetime.datetime(2013, 5, 8, 0, 0)

Upvotes: 0

Views: 3725

Answers (2)

Jared Knipp
Jared Knipp

Reputation: 5950

You need to inject/interpolate the date by using parameters. SQL server is attempting to run the SQL statement as is and expects a datetime.datetime(..) function to exist on the database. 

cursor.execute("SELECT isnull(SOP30200.SOPNUMBE,''), isnull(SOP30200.docdate,'') from SOP30200 where SOP30200.docdate > ?", datetime.datetime(2015,1,1,0,0))

See http://mkleehammer.github.io/pyodbc/ - Parameters section

Upvotes: 4

Dmitry Kasatsky
Dmitry Kasatsky

Reputation: 316

"datetime.datetime" is not the SQL function, is the class of Python standard library.

May be:

cursor.execute("SELECT isnull(SOP30200.SOPNUMBE,''), isnull(SOP30200.docdate,'') from SOP30200 where SOP30200.docdate > ?", datetime.datetime(2015,1,1,0,0))

Upvotes: 2

Related Questions