Can't UPDATE date =? SQL server from JSON with Python

Question

Please help, have tried a lot of different ways to solve the problem - can't insert date in column with date/datetime/datetimeoffset db in SQL server. I have different errors depending on tries:

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

pyodbc.DataError: ('22007', '[22007] [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string. (241) (SQLExecDirectW)')

this is origin type of json i've tried datetime.date() i've tried cast in query but all in vain found suggestion to add {SQL Server Native Client 11.0} the same convertion error

tried this:

def main():
    for i in list(range(0,40,1)):
        for item in voucherlist(bearer, accept, i)['content']:
            #voucherId=item['id']
            dataFrameVoucherItems=pandas.DataFrame(vouchers(bearer, accept, voucherId))
            pmnt = payment(bearer, accept, voucherId)
            dataFrame = pandas.concat([dataFrameVouchers, dataFrameVoucherItems], ignore_index= True, sort = False)
            tuples = [tuple(x) for x in dataFrame.to_numpy()]
            f = '%Y-%m-%d'
            print(item['voucherDate'])
            year = int(item['voucherDate'][:4])
            month = int(item['voucherDate'][6:7])
            day = int(item['voucherDate'][9:10])
            voucherDate = str(datetime.date(year,month,day))
            date = datetime.datetime.strptime(voucherDate,f)
            print(date)
            a = (voucherId, pmnt, voucherId,date)
            for t in tuples:
                t = a + t
                print(t)
                cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
                cursor = cnxn.cursor()
                query = "IF EXISTS (SELECT * FROM vouchers WHERE voucher = ?) BEGIN SET DATEFORMAT ydm UPDATE vouchers SET openAmount = ?, voucherDate= ? WHERE voucher = ? END ELSE BEGIN INSERT INTO vouchers (voucher, organizationId, voucherType, voucherStatus, voucherNumber, voucherDate, shippingDate, dueDate, totalGrossAmount, totalTaxAmount,taxType, useCollectiveContact, contactId, remark, amount, taxAmount, taxRatePercent, categoryId, filesChar, createdDate, updatedDate, version) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) END"
                cursor.execute(query,t)
                cnxn.commit()
                cursor.close()

no result

sql query is executed

I have new error

[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Implicit conversion from data type datetime to decimal is not allowed. Use the CONVERT function to run this query.

when wrote

query = "IF EXISTS (SELECT * FROM vouchers WHERE voucher = ?) BEGIN SET DATEFORMAT ydm UPDATE vouchers SET openAmount = convert(datetime,?,105), voucherDate= ? WHERE voucher = ? END ELSE BEGIN INSERT INTO vouchers (voucher, organizationId, voucherType, voucherStatus, voucherNumber, voucherDate, shippingDate, dueDate, totalGrossAmount, totalTaxAmount,taxType, useCollectiveContact, contactId, remark, amount, taxAmount, taxRatePercent, categoryId, filesChar, createdDate, updatedDate, version) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) END"

and one more:

pyodbc.DataError: ('22018', '[22018] [Microsoft][SQL Server Native Client 11.0][SQL Server]Operand type clash: date is incompatible with decimal (206) (SQLExecDirectW); [22018] [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared. (8180)')

[Please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) — Thom A, May 28 '22 at 16:20
`#SET QUOTED_IDENTIFIER OFF_ ` No - do NOT go down this path. Write your SQL so that it works correctly without resorting to kludges — SMor, May 28 '22 at 18:09
I don't use it (it's quoted, haven't deleted it - used for adding element, when had problems for adding comments with quotes), in sql server it's fine to UPDATE voucher SET voucherDate = '2022-12-31' WHERE voucherId = '..........' — Elina, May 28 '22 at 19:10
always put code, data and full error message as text (not screenshot, not link) in question (not in comment). It will be more readable and easier to use in answer, and more people will see it - so more people can help you. — furas, May 28 '22 at 20:27
always put full error message (starting at word "Traceback") in question (not in comments) as text (not screenshot, not link to external portal). There are other useful information. — furas, May 28 '22 at 20:28

score 0 · Answer 1 · answered May 29 '22 at 15:24

I've found solution thanks this post How to create a Date in SQL Server given the Day, Month and Year as Integers

for item in voucherlist(bearer, accept, i)['content']:
        voucherId=item['id']
        dataFrameVoucherItems=pandas.DataFrame(vouchers(bearer, accept, voucherId))
        pmnt = payment(bearer, accept, voucherId)
        dataFrame = pandas.concat([dataFrameVouchers, dataFrameVoucherItems], ignore_index= True, sort = False)
        #dataFrame.to_csv(filename, sep='\t', encoding ='utf-8')
        tuples = [tuple(x) for x in dataFrame.to_numpy()]
        f = '%Y-%m-%d %H:%M:%S.%f+%Z'
        f2 = '%Y-%m-%d'
        voucherYear = int(item['voucherDate'][:4])
        voucherMonth = int(item['voucherDate'][6:7])
        voucherDays = int(item['voucherDate'][9:10])
        a = (voucherId, voucherYear, voucherMonth, voucherDays, pmnt, voucherId)
        for t in tuples:
            t = a + t
            print(t)
            cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
            cursor = cnxn.cursor()
            query = "IF EXISTS (SELECT * FROM vouchers WHERE voucher = ?) BEGIN declare @year int = ?,  @month int = ?, @days int = ? UPDATE vouchers SET openAmount = ?, voucherDate= cast(cast(@year*10000 + @month*100 + @days as varchar(255)) as date) WHERE voucher = ? END ELSE BEGIN INSERT INTO vouchers (voucher, organizationId, voucherType, voucherStatus, voucherNumber, voucherDate, shippingDate, dueDate, totalGrossAmount, totalTaxAmount,taxType, useCollectiveContact, contactId, remark, amount, taxAmount, taxRatePercent, categoryId, filesChar, createdDate, updatedDate, version) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) END"
            cursor.execute(query,t)
            cnxn.commit()
            cursor.close()

score 0 · Answer 2 · answered May 29 '22 at 19:51

have found better solution! thanks: Python does not match format '%Y-%m-%dT%H:%M:%S%Z.%f'

for i in list(range(0,40,1)):
    for item in voucherlist(bearer, accept, i)['content']:
        voucherId=item['id']
        dataFrameVoucherItems=pandas.DataFrame(vouchers(bearer, accept, voucherId))
        pmnt = payment(bearer, accept, voucherId)
        dataFrame = pandas.concat([dataFrameVouchers, dataFrameVoucherItems], ignore_index= True, sort = False)
        dataFrame.info()
        #dataFrame.to_csv(filename, sep='\t', encoding ='utf-8')
        tuples = [tuple(x) for x in dataFrame.to_numpy()]
        #f = '%Y-%m-%dT%H:%M:%S.%f+%Z'
        f1=datetime.fromisoformat(item['voucherDate'])
        print(f1)
        a = (voucherId, f1, pmnt, voucherId)
        for t in tuples:
            t = a + t
            print(t)
            cnxn = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
            cursor = cnxn.cursor()
            query = "IF EXISTS (SELECT * FROM vouchers WHERE voucher = ?) BEGIN declare @year datetime = ? UPDATE vouchers SET openAmount = ?, voucherDate= @year WHERE voucher = ? END ELSE BEGIN INSERT INTO vouchers (voucher, organizationId, voucherType, voucherStatus, voucherNumber, voucherDate, shippingDate, dueDate, totalGrossAmount, totalTaxAmount,taxType, useCollectiveContact, contactId, remark, amount, taxAmount, taxRatePercent, categoryId, filesChar, createdDate, updatedDate, version) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) END"
            cursor.execute(query,t)
            cnxn.commit()
            cursor.close()

Can't UPDATE date =? SQL server from JSON with Python

2 Answers2