Insert date and time into MS SQL table using pyodbc

Question

I am trying to insert a datetime value into an 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 Invalid syntax next to '07', which I think is the number after the date and starts the time.

Also I tried this:

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

and now this error occurs:

Conversion error while converting date and / or time from character string.

Answer 1

Remove the conversion of date and time to string and use the options instead:

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

Answer 2

You can also use datetime.strftime () to convert the datetime object to a string as you need. str (datetime) is a bad idea.

currenttime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S.%f") cursor.execute("""insert into currentvalue(value1,currentdatetime) values(55,"""+ currenttime+")")