8

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.

scharfmn
  • 3,561
  • 7
  • 38
  • 53

4 Answers4

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()) + "'
    )
")
tanaydin
  • 5,171
  • 28
  • 45
  • 5
    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. – scharfmn Apr 11 '13 at 14:39
  • @tanaydin in the answer you said "mysql", shouldn't that be "MS SQL"? – SHernandez Jun 09 '15 at 19:51
  • 1
    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? – SHernandez Jul 17 '15 at 10:56
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)
Thanasis Petsas
  • 4,378
  • 5
  • 31
  • 57
  • 2
    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. – Thanasis Petsas Apr 10 '13 at 21:24
  • 2
    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 Apr 10 '13 at 22:11
  • See below. I must have a bug. – scharfmn Apr 10 '13 at 22:14
  • 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: http://mysql-python.sourceforge.net/MySQLdb.html – Thanasis Petsas Apr 10 '13 at 22:20
  • What about using `MySQLdb` module? – Thanasis Petsas Apr 10 '13 at 22:21
  • I tried on a simple test table (just one string). It worked, and I didn't need to specify the column name, but I'll try. Have to use SQL Server - will MySQLdb work with that? – scharfmn Apr 10 '13 at 22:24
  • I am sorry, I am not sure about that. Maybe it would be useful to show us some SQL code. Please try to make your example to http://sqlfiddle.com/ and send us the URL. – Thanasis Petsas Apr 10 '13 at 22:35
  • Thank you very much. It was a field length issue. Please see below. – scharfmn Apr 11 '13 at 14:41
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

Teja
  • 141
  • 1
  • 6
  • 1
    Strftime is more explicit, it seems to me, but then again this is a field length issue, and your solution speaks to that directly. – scharfmn Mar 13 '14 at 14:01
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.

rocktheartsm4l
  • 2,129
  • 23
  • 38