inserting date time with microsecond into SQL Server from python

Question

Below code works perfectly:

import pypyodbc
import datetime

connection = pypyodbc.connect('Driver={SQL Server};'
    'Server=some_server;'
    'Database=some_db')
cur = connection.cursor()
some_time = datetime.datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S')
query = "insert into table_a (date_created) values ('"+some_time+"')"
cur.execute(query)
connection.commit()
connection.close()

But if I change (adding microseconds to date)

some_time = datetime.datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S.%f')

it generates error:

DataError: ('22007', '[22007] [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string.')

date_created column is of datetime type and does display microseconds.

Any thoughts?

Humor me... Does inserting a hard coded string instead of the variable with milliseconds work? Does setting some_time to a string '2018-09-25 11:45:30.123' work without going through the datetime conversions? — TEEKAY, Sep 26 '18 at 15:50
@TEEKAY correct, the following works fine: `some_time = datetime.datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S')` — user1700890, Sep 26 '18 at 15:51

score 4 · Answer 1 · answered Sep 28 '18 at 17:02

SQL Server datetime columns are only able to store fractional seconds to millisecond precision (3 decimal places). When you do

some_time = datetime.datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S.%f')

you get a string formatted to the precision of Python's datetime, which is microseconds (6 decimal places)

>>> some_time
'2018-09-28 16:38:14.344801'

and SQL server doesn't like the extra three decimal places.

The solution is to not format the datetime as a string, just pass the datetime value itself in a proper parameterized query

query = "insert into table_a (date_created) values (?)"
params = (datetime.datetime.utcnow(), )
cur.execute(query, params)

inserting date time with microsecond into SQL Server from python

1 Answers1