MSSQL time is not round-tripable

[hb2638]

Please first make sure you have looked at:

• Documentation: https://github.com/mkleehammer/pyodbc/wiki
• Other issues

Environment

To diagnose, we usually need to know the following, including version numbers. On Windows, be sure to specify 32-bit Python or 64-bit:

• Python: 3.11
• pyodbc: 4.0.35
• OS: Windows
• DB: Microsoft SQL Server 2019
• driver: MSQL ODBC v18

Issue

python datetime.time is not round-tripable.. Getting precision loss when reading back the value thay was sent to the server

Often it is easiest to describe your issue as "expected behavior" and "observed behavior".

The below code fails with AssertionError: datetime.time(1, 23, 45, 12345) != datetime.time(1, 23, 45)

def test_time_round_trip(self):
    import pyodbc
    conn = pyodbc.connect("Driver=ODBC Driver 18 for SQL Server;Server=.;TrustServerCertificate=yes;Trusted_Connection=yes;MARS_Connection=yes;ConnectRetryCount=30")
    cursor = conn.execute("DECLARE @FOO TIME='01:23:45.012345678';SELECT @FOO AS [FOO];")
    expected = cursor.fetchone().FOO
    cursor.close()
    cursor = conn.execute("DECLARE @FOO TIME=?;SELECT @FOO AS [FOO];", [expected])
    actual = cursor.fetchone().FOO
    self.assertEqual(expected, actual)

[gordthompson]

https://github.com/mkleehammer/pyodbc/wiki/Tips-and-Tricks-by-Database-Platform#time-columns

[v-chojas]

Also note that time resolution in SQL Server only reaches 0.1us.

[hb2638]

Hi @gordthompson and @v-chojas . Thx!! I didn't know it was "by-design"... I'll close. Thx for the "tips and tricks" link... I was unaware of the setinputsizes function. Very good to know.