CODEWITHSUNDEEP
pythonsql-serverwindowspymssql
scharfmn
scharfmn

Reputation: 3661

Using pymssql to insert datetime object into SQL Server

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.

Upvotes: 8

Views: 21498

Answers (4)

tanaydin
tanaydin

Reputation: 5296

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()) + "'
    )
")

Upvotes: 6

rocktheartsm4l
rocktheartsm4l

Reputation: 2187

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.

Upvotes: 0

Teja
Teja

Reputation: 141

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

Upvotes: 4

Thanasis Petsas
Thanasis Petsas

Reputation: 4448

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)

Upvotes: 5

Related Questions