0

When inserting certain datetime values to a SQL Server DB with pyodbc, the fractional seconds are changed.

Setup:

>>> import pyobdc
>>> from datetime import datetime
>>> cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};...')
>>> cursor = cnxn.cursor()
>>> cursor.execute("CREATE TABLE [dbo].[test]([testid] [int] NOT NULL,[testdatetime] [datetime] NOT NULL)")
<pyodbc.Cursor object at 0x7fb02d8d7b30>
>>> cnxn.commit()

Inserting datetime(2022,1,2,3,4,5,6000) will yield datetime(2022,1,2,3,4,5,7000) (though using this value in the WHERE statement will not work):

>>> cursor.execute("INSERT INTO test(testid, testdatetime) VALUES (?, ?)", 1, datetime(2022,1,2,3,4,5,6000))
<pyodbc.Cursor object at 0x7fb02d8d7b30>
>>> cnxn.commit()
>>> cursor.execute("SELECT * FROM test").fetchall()
[(1, datetime.datetime(2022, 1, 2, 3, 4, 5, 7000))]
>>> cursor.execute("SELECT * FROM test WHERE testdatetime = ?", datetime(2022,1,2,3,4,5,6000)).fetchall()
[]
>>> cursor.execute("SELECT * FROM test WHERE testdatetime = ?", datetime(2022,1,2,3,4,5,7000)).fetchall()
[]

Inserting datetime(2022,1,2,3,4,5,640000) works as expected:

>>> cursor.execute("INSERT INTO test(testid, testdatetime) VALUES (?, ?)", 2, datetime(2022,1,2,3,4,5,640000))
<pyodbc.Cursor object at 0x7fb02d8d7b30>
>>> cnxn.commit()
>>> cursor.execute("SELECT * FROM test").fetchall()
[(1, datetime.datetime(2022, 1, 2, 3, 4, 5, 7000)), (2, datetime.datetime(2022, 1, 2, 3, 4, 5, 640000))]
>>> cursor.execute("SELECT * FROM test WHERE testdatetime = ?", datetime(2022,1,2,3,4,5,640000)).fetchall()
[(2, datetime.datetime(2022, 1, 2, 3, 4, 5, 640000))]

Why is this happening?

Python version 3.8.8
pyodbc version 4.0.32
SQL Server 2019 version 15.0.4053.23

Serge Hauri
  • 313
  • 1
  • 5
  • 2
    The SQL Server `datetime` data type does not have micrososecond resolution. For legacy reasons, i.e.: UNIX system clock ticks, it's 3 1/3ms resolution (300 ticks per second). – AlwaysLearning Apr 12 '22 at 11:23
  • 2
    Consider `datatime2` for up to 7 fractional seconds precision (100 nanosecond). – Dan Guzman Apr 12 '22 at 11:39

0 Answers0