Inserting datetime into a MS SQL table using pyodbc

Question

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.

Use parameters.

Bryan
– Bryan

2014-06-27 18:12:35 +00:00
Commented Jun 27, 2014 at 18:12 — Bryan
– Bryan, Commented Jun 27, 2014 at 18:12

Bryan · Accepted Answer · 2014-06-27 18:14:57Z

20

Remove the datetime to string conversion and instead use parameters:

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

answered Jun 27, 2014 at 18:14

Bryan

17.7k7 gold badges59 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rich Over a year ago

I experimented. Using parameters, inserting datetimes, or strings in "roughly ISO" format ("2024-09-12 11:39:57"), in ISO format with 'T' in the middle ("2024-09-12T11:39:57") or even with a final timezone marker ("2024-09-12T11:39:57Z") works fine, and string values also work with milliseconds (e.g., "2024-09-12 11:39:57.123"), but datetime.now().isoformat() returns a string with MICROSECONDS ("2024-09-12T11:39:57.123456"), which fails: "Conversion failed when converting date and/or time from character string. (241)". If you have a string already, chop microseconds off or convert to datetime.

cmpara · Accepted Answer · 2015-01-30 13:39:04Z

7

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

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

answered Jan 30, 2015 at 13:39

cmpara

792 silver badges3 bronze badges

1 Comment

Michele d'Amico Over a year ago

I think your solution not work and it is just like the questioner first attempt. You need at least a string escape.

Chris · Accepted Answer · 2023-04-12 09:04:52Z

-1

You can add single quotes:

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

edited Apr 12, 2023 at 9:04

Chris

139k139 gold badges316 silver badges293 bronze badges

answered Apr 8, 2023 at 3:04

TomN

12 bronze badges

2 Comments

Chris Over a year ago

Building SQL queries by sticking strings together is a bad habit

TomN Over a year ago

@Chris Thank you for the feedback. I didn't know that I was writing vulnerable SQL injection code. Moving forward, I will keep that in mind. See: realpython.com/prevent-python-sql-injection for how to write safer code for SQL injection attacks.

Collectives™ on Stack Overflow

Inserting datetime into a MS SQL table using pyodbc

3 Answers 3

1 Comment

1 Comment

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

2 Comments

Linked

Related