Using pymssql to insert datetime object into SQL Server

Question

How do I insert a datatime object using pymssql? I know that the SQL Server table is expecting a datetime object, let's say in position 3. I've tried all three of these:

cursor.execute("INSERT INTO MyTable VALUES(1, 'Having Trouble', datetime.datetime.now())")
cursor.execute("INSERT INTO MyTable VALUES(1, 'Having Trouble', 20130410)")
cursor.execute("INSERT INTO MyTable VALUES(1, 'Having Trouble', '20130410')")
cursor.execute("INSERT INTO MyTable VALUES(1, 'Having Trouble', GETDATE())")

and I get the same error each time:

OperationalError: (241, 'Conversion failed when converting date and/or time from character string.DB-Lib error message 241, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n')

I've scoured the little documentation there is, and searched repeatedly.

EDIT: Secondary problem was a field-length problem. See the first comment on the accepted answer.

tanaydin · Accepted Answer · 2015-08-25 06:41:58Z

6

you are trying to insert a string that is not formated as date (datetime.datetime.now(), 20130410, '20130410', GETDATE()) so sql server can't parse date from it...

so try this...

cursor.execute("
    INSERT INTO MyTable
    VALUES(
        1,
        'Having Trouble',
        '" + str(datetime.datetime.now()) + "'
    )
")

edited Aug 25, 2015 at 6:41

answered Apr 10, 2013 at 21:05

tanaydin

5,32632 silver badges50 bronze badges

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

3 Comments

scharfmn Over a year ago

This is it. It was a field length problem. datetime.datetime.now() gives me '2013-04-11 10:08:29.512000'. I tried that in SQL Server Management Studio, and it failed. But '2013-04-11 10:08:29.512' works. So I am doing

cursor.execute("INSERT INTO MyTable VALUES(2, 'having trouble', '" + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') + "')")

and it is working. (Also re: format: it may also want the hyphens explicitly - not sure.) Thank you very much for all your help.

SHernandez Over a year ago

@tanaydin in the answer you said "mysql", shouldn't that be "MS SQL"?

SHernandez Over a year ago

isnt it safer to always use datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') - the output of str could change somehow and it is not very ecplicit, isnt it just a coincidence it fits?

Thanasis Petsas · Accepted Answer · 2013-04-10 21:22:59Z

5

You can use this code:

# a tuple with the data to be stored in db
data = (1, 'Having Trouble', datetime.datetime.now())
# perform the query 
cursor.execute("INSERT INTO MyTable VALUES(%s, %s, %s)" % data)

edited Apr 10, 2013 at 21:22

answered Apr 10, 2013 at 21:17

Thanasis Petsas

4,4685 gold badges34 silver badges57 bronze badges

8 Comments

Thanasis Petsas Over a year ago

tanaydin's solution is right! Here I suggest youa bit more flexible way using tuples to store your data before inserting them to the database.

scharfmn Over a year ago

I understand. Had tried it that way first. Here's what I get (my table is actually expecting two datetimes):

sqldata = (2, 'url', 'raw', 'uni', 'text', 'mark', 'auth', 'ttitle', 'wtitle', datetime.datetime.now(), datetime.datetime.now(), 'ctype')

and then cursor.execute("INSERT INTO WebContent VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" % sqldata) which yields

ProgrammingError: (102, "Incorrect syntax near '18'.DB-Lib error message 102, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")

scharfmn Over a year ago

See below. I must have a bug.

Thanasis Petsas Over a year ago

Is the order of the columns in sqldata variable the same with the one of the WebContent table?? Maybe you need to specify the column names of the table before the VALUES. Some examples can be found here: mysql-python.sourceforge.net/MySQLdb.html

Thanasis Petsas Over a year ago

What about using MySQLdb module?

|

Teja · Accepted Answer · 2014-03-10 20:08:52Z

4

Try this out:

timeStamp = str(datetime.datetime.now())[0:-3]

This time stamp format can be converted by MS SQL SERVER and can be used in pymssql to insert an object of type datetime

edited Mar 10, 2014 at 20:08

user2033018

answered Mar 10, 2014 at 19:46

Teja

1411 silver badge6 bronze badges

1 Comment

scharfmn Over a year ago

Strftime is more explicit, it seems to me, but then again this is a field length issue, and your solution speaks to that directly.

rocktheartsm4l · Accepted Answer · 2014-08-08 21:47:50Z

0

For others facing this same issue my problem was different.

My year was getting parsed as 0014; which I thought was being interpreted as 2014. Took me a while to realize what was happening.

Where pymssql comes in is that the smalldate type didn't recognize 0014 as a year and was unable to make the conversion.

answered Aug 8, 2014 at 21:47

rocktheartsm4l

2,18723 silver badges39 bronze badges

Collectives™ on Stack Overflow

Using pymssql to insert datetime object into SQL Server

4 Answers 4

3 Comments

8 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

8 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related