-1

When I run the following iwth the value of Job_date being "2023-01-02T21:00:00.504Z"

@api.route('/addjob', methods=["POST"])
def addjob():
   f = open('addjob.txt','w')
   f.write(str(request.json))
   cursor = mysql.connection.cursor()
   
   sql = '''INSERT into jobs (job_date,cust_auto_id,cost,paid,mileage,job_desc,person_id) values (str_to_date(%s,"%%Y-%%m-%%dT%%H:%%i:%%sZ"),%s,%s,%s,%s,%s,%s)'''
   try:
       cursor.execute(sql,(request.json.get("jobdate", None),request.json.get("whichauto", None),request.json.get("jobcost", None),request.json.get("paystatus", None),request.json.get("mileage", None),request.json.get("jobdesc", None),request.json.get("whichperson", None)))
       mysql.connection.commit()
   except Exception as e:
       f.write(str(e))
   
   return jsonify([])

I get

"Incorrect datetime value: '2023-01-02T21:00:00.504Z' for function str_to_date
BostonMacOSX
  • 1,369
  • 2
  • 17
  • 38
  • 1
    Right after the table name you only put the list of columns. Expressions belong in the values part, so put a str to date there. But your parameter replacement in your client is using % as a special character, you will need to figure out how to get the literal % chars needed in the format string past that – ysth Jan 02 '23 at 23:37
  • @ysth updated the code above.... – BostonMacOSX Jan 03 '23 at 00:08

1 Answers1

0

In mysql 8, you can specify an %f format to accept up to 6 digits of partial second:

str_to_date(%s,"%%Y-%%m-%%dT%%H:%%i:%%s.%%fZ")

In earlier versions or mariadb, you can simply remove the Z instead of using str_to_date:

replace(%s,"Z","")
ysth
  • 96,171
  • 6
  • 121
  • 214