CODEWITHSUNDEEP
pythonsql-serverdatetimeinsertpypyodbc
user1700890
user1700890

Reputation: 7730

inserting date time with microsecond into SQL Server from python

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?

Upvotes: 1

Views: 6141

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123399

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)

Upvotes: 5

Related Questions