Using pymssql to insert a datetime object in SQL Server

Question

How to insert a datatime object using pymssql? I know that a SQL Server table expects a datetime object, say, at position 3. I tried all three of them:

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 every time I get the same error:

 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 looked through a little documentation and searched repeatedly.

Answer 1

you are trying to insert a row that is not formed as date (datetime.datetime.now (), 20130410, '20130410', GETDATE ()), so the sql server cannot parse the date from it ...

try it...

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

Answer 2

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) 

Answer 3

Try the following:

 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

Answer 4

For others facing this problem, my problem was different.

My year was parsed as 0014; which I thought was interpreted as 2014. It took time to understand what was happening.

Where pymssql comes from is that the smalldate type did not recognize 0014 as a year and could not perform the conversion.