Operand type clash with parameterized UPDATE query

Question

I have a SQL Server table with the following columns (* indicates NULLs are allowed):

ID (int)
RequisitionID (int)
ApplicantID (int)
WorkflowStateID (int)
WorkflowDate (datetime)
SubmissionDate (datetime)
StartDate (date)*
DispositionID (int)*
ATSApplicationID (varchar(255))*
ApplicationLink (varchar(255))*
CreateDate (datetime)
ModifyDate (datetime)

I am attempting a parameterized INSERT query using pypyodbc with Driver=SQL Server (MS SQL Server 2012 database). Here is the query:

sql_statement = """
    UPDATE [hr].[ATS.Applications] SET RequisitionID = ?, WorkflowStateID = ?, WorkflowDate = ?, StartDate = ?, DispositionID = ?, ModifyDate = ? WHERE ID = ?
    """

cursor.execute(sql_statement,(new_application.requisition_id,
                              new_application.workflow_state,
                              new_application.workflow_date.strftime("%Y-%m-%dT%H:%M:%S"),
                              new_start_date,
                              new_application.disposition,
                              now,
                              old_application.ID))

cursor.commit()

Resulting in the following error:

DataError: (u'22018', u'[22018] [Microsoft][ODBC SQL Server Driver][SQL Server]Operand type clash: int is incompatible with date')

I assume one of the date strings is being interpreted as a calculated integer, however using the debugger, all data & data types appear to be compatible with my table:

new_application.requisition_id = {int} 7
new_application.workflow_state = {int} 19
new_application.workflow_date.strftime("%Y-%m-%dT%H:%M:%S") = {str} '2017-05-09T07:52:13'
new_start_date = {NoneType} None
new_application.disposition = {int} 7
now = {str} '2017-05-09T17:55:27'
old_application.ID = {int} 34402

I've tried inserting datetime values as both datetime objects and strings, and can't seem to resolve what data type it's expecting.

Any ideas why I am having issues with this UPDATE statement?

Thank you for the quick response! By resultant query do you mean the SQL query with all '?' replaced with the values? My debugger cannot show this with how the code is written, as the parameters are being replaced upon query execute. — user1944673
I still receive the same error "int is incompatible with date." I assume both the "now" and "workflow_date" could be causing the issue here. It seems to be really finicky with what format it accepts datetime values -- I've tried as type datetime and as type string and can't figure out which way is correct. — user1944673
which database driver are you using? there definitely should be support of converting datetime objects for query bindings — Azat Ibrakov
I'm using Driver={SQL Server}. I've had this working successfully in the past but didn't understand why. Now that the issue has come up again I'm hoping to get a better understanding of the failure. — user1944673

Bryan Bryan · Accepted Answer · 2017-05-10T16:21:53

Let pypyodbc handle the type conversion from Python date/datetime objects to the appropriate SQL Server data type, instead of relying on string formatting.

In the example below, I'm assuming:

new_application.workflow_date is Python datetime object
now from original question is intended to get current system time using the datetime module

sql_statement = """
UPDATE [hr].[ATS.Applications] SET RequisitionID = ?, WorkflowStateID = ?, WorkflowDate = ?, StartDate = ?, DispositionID = ?, ModifyDate = ? WHERE ID = ?
    """

cursor.execute(sql_statement,(new_application.requisition_id,
                              new_application.workflow_state,
                              new_application.workflow_date,
                              new_start_date,
                              new_application.disposition,
                              datetime.now(),
                              old_application.ID))

cursor.commit()

For a supporting example including SQL Server DATE and TIME types see my answer here.

Operand type clash with parameterized UPDATE query

1 Answers