Odd behavior with MS SQL and datetimes -- INSERT, SELECT values match but WHERE values don't match

[IsaacG]

Environment

• Python: 3.10
• pyodbc: 5.1.0
• OS: Ubuntu
• DB: MS SQL Server
• driver: msodbcsql

Issue

datetime objects with 3 digits of milliseconds can be inserted into the DB. When selected, the values coming out match the values going in, so the conversion in and out seems to work. However, when using the same value for a WHERE clause, the values do not match.

Excepted: using the inserted datetime object in a WHERE clause should find the row.

Actual: some datetime objects which were inserted/selected cannot be used to select said row.

Test code:

t1 = datetime.datetime(2024, 5, 6, 7, 8, 9, microsecond=120000)
t2 = datetime.datetime(2024, 5, 6, 7, 8, 9, microsecond=123000)

connection = ts_db.tsodbc.tsconnect("dryver=msodbcsql;server=...;database=...")
cursor = connection.cursor()
cursor.execute("DROP TABLE IF EXISTS datetime_test")
cursor.execute("CREATE TABLE datetime_test (date DATETIME)")
cursor.execute("INSERT INTO datetime_test (date) VALUES (?)", t1)
cursor.execute("INSERT INTO datetime_test (date) VALUES (?)", t2)
cursor.commit()

print(cursor.execute("SELECT date FROM datetime_test").fetchall())
print(cursor.execute("SELECT date FROM datetime_test WHERE date = ?", t1).fetchall())
print(cursor.execute("SELECT date FROM datetime_test WHERE date = ?", t2).fetchall())

Actual output:

» python3 db_test.py
[(datetime.datetime(2024, 5, 6, 7, 8, 9, 120000),), (datetime.datetime(2024, 5, 6, 7, 8, 9, 123000),)]
[(datetime.datetime(2024, 5, 6, 7, 8, 9, 120000),)]
[]

Expected output:

» python3 db_test.py
[(datetime.datetime(2024, 5, 6, 7, 8, 9, 120000),), (datetime.datetime(2024, 5, 6, 7, 8, 9, 123000),)]
[(datetime.datetime(2024, 5, 6, 7, 8, 9, 120000),)]
[(datetime.datetime(2024, 5, 6, 7, 8, 9, 123000),)]

Is this expected behavior or is there a bug here?

[gordthompson]

Do you get the expected output for the last query if you use this?

print(cursor.execute("SELECT date FROM datetime_test WHERE date = ?", str(t2)[:23]).fetchall())

[IsaacG]

Do you get the expected output for the last query if you use this?

print(cursor.execute("SELECT date FROM datetime_test WHERE date = ?", str(t2)[:23]).fetchall())

Yes. I do. That returns the expected row.

Should I be converting all datetime objects to strings prior to using them in a WHERE clause?

The actual code I'm working on gets a bunch of rows then updates select rows in specific ways. I was getting a datetime object from the result of one SELECT and trying to use that same object in a subsequent UPDATE ... WHERE clause. It seems dodgy that I'd get a datetime then need to convert it to a string to use it.

rows = cursor.execute("SELECT ... WHERE ...").fetchall()
for row in rows:
    new_value = modify(row.column_one)
    cursor.execute("UPDATE ... SET value = ? WHERE ... AND datetime = ?", new_value, row.datetime)

[gordthompson]

This appears to be a limitation when working with SQL Server's datetime column type. The same issue does not occur when the column is datetime2.

With a datetime column, the code

print(cursor.execute("SELECT date FROM datetime_test WHERE date = ?", t2).fetchall())

results in this being executed on the server

declare @p1 int
set @p1=3
exec sp_prepexec @p1 output,N'@P1 datetime2',N'SELECT date FROM datetime_test WHERE date = @P1','2024-05-06 07:08:09.1230000'
select @p1

As you noted, this works okay for .120000 but not for .123000. Interestingly enough, this query

SELECT date FROM datetime_test WHERE date = '2024-05-06 07:08:09.123000'

fails with

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

whereas this one succeeds and returns the correct row

SELECT date FROM datetime_test WHERE date = '2024-05-06 07:08:09.123'

which is why the str(t2)[:23] hack works.

[IsaacG]

That's odd. Do you know why the INSERT works fine with that datetime? The INSERT line shows it successfully converting that very same datetime object to a value that gets inserted.

If the column precision is 3, should pyodbc be truncating the string to 3 digits of milliseconds before passing the string on to the server?

[gordthompson]

Do you know why the INSERT works fine with that datetime?

SQL Server does appear to be inconsistent, and there may be some low-level reason for it on the server side, but I don't know why.

If the column precision is 3, should pyodbc be truncating the string to 3 digits of milliseconds before passing the string on to the server?

That would be nice, but it would also require pyodbc to essentially prepare every statement before execution (so it can then inspect the column types) which would add additional round-trips to the server. It looks like pyodbc used to always prepare (years ago), but then that behaviour was "optimized out". Discussed here: https://github.com/mkleehammer/pyodbc/issues/214