Search code examples
pythonsql-serverwindowspymssql

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.

Solution

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