Inserting datetime into a MS SQL table using pyodbc

user3784140 picture user3784140 · Jun 27, 2014 · Viewed 20.1k times · Source

I'm trying to insert a datetime value into a MS SQL Server table using pyodbc. If I do it manually, something like:

cursor.execute("""insert into currentvalue(value1,currentdatetime)
                                    values(55,'2014-06-27 16:42:48.533')""")

I have no problem at all, but when I try to do:

currenttime = str(datetime.datetime.now())
cursor.execute("""insert into currentvalue(value1,currentdatetime) 
                                    values(55,"""+ currenttime+")")

I got this error:

SQL server Incorrect syntax near '07' which i think is the number after the date and starting the time.

Also I tried this:

currenttime = "'"+str(datetime.datetime.now())+"'"

and now this error comes up:

Conversion failed when converting date and/or time from character string.

Answer

Bryan picture Bryan · Jun 27, 2014

Remove the datetime to string conversion and instead use parameters:

....
cursor.execute("insert into currentvalue (value1,currentdatetime) values(?,?)",
               (value1, datetime.datetime.now()))
....