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, May 10 '17 at 01:41
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, May 10 '17 at 01:55
which database driver are you using? there definitely should be support of converting `datetime` objects for query bindings — Azat Ibrakov, May 10 '17 at 02:26
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, May 10 '17 at 02:36
Sometimes it is None sometimes it is not None. I'm not sure if that is affecting the issue here. This is the closest thread I have found to helping:http://stackoverflow.com/questions/14119133/conversion-failed-when-converting-date-and-or-time-from-character-string-while-i — user1944673, May 10 '17 at 04:06
have you tried execute query with all bindings set, without `?` characters? or a simple query without datetime fields? — Azat Ibrakov, May 10 '17 at 04:24

score 1 · Accepted Answer · edited May 23 '17 at 12:26

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.

I removed "new_start_date" from the query and it worked. This variable was being converted from a date object to a string object using .strftime("%Y-%m-%d"), which was being interpreted as an integer (i.e. 2017 minus 5 minus 10). — user1944673, May 11 '17 at 01:33

Operand type clash with parameterized UPDATE query

1 Answers1