cursor.bulkcopy() rounds DATETIME one tick lower than execute()

[dlevy-msft-sql]

Summary

cursor.bulkcopy() writes SQL datetime values one tick lower than cursor.execute() for the same Python datetime input.

For the same Python value, execute() stores the expected SQL Server datetime tick (.1233333), while bulkcopy() stores the previous tick (.1200000).

This reproduces on both:

• SQL Server 2022
• Fabric SQL database

Environment

• mssql-python 1.5.0
• Windows
• Python 3.14.0

Minimal repro

import mssql_python
from datetime import datetime

conn_str = "Server=<server>;Database=<database>;UID=<user>;PWD=<password>;TrustServerCertificate=yes;Encrypt=yes"
value = datetime(2025, 5, 16, 16, 33, 33, 123000)

with mssql_python.connect(conn_str) as conn:
    with conn.cursor() as cursor:
        cursor.execute("IF OBJECT_ID('dbo.DateDriftExecute', 'U') IS NOT NULL DROP TABLE dbo.DateDriftExecute")
        cursor.execute("IF OBJECT_ID('dbo.DateDriftBulkcopy', 'U') IS NOT NULL DROP TABLE dbo.DateDriftBulkcopy")
        cursor.execute("CREATE TABLE dbo.DateDriftExecute (ModifiedDate datetime NOT NULL)")
        cursor.execute("CREATE TABLE dbo.DateDriftBulkcopy (ModifiedDate datetime NOT NULL)")
    conn.commit()

    with conn.cursor() as cursor:
        cursor.execute("INSERT INTO dbo.DateDriftExecute (ModifiedDate) VALUES (?)", (value,))
    conn.commit()

    with conn.cursor() as cursor:
        cursor.bulkcopy(
            "dbo.DateDriftBulkcopy",
            [(value,)],
            batch_size=1,
            table_lock=True,
            timeout=3600,
        )
    conn.commit()

    with conn.cursor() as cursor:
        cursor.execute(
            "SELECT 'execute' AS path, "
            "CONVERT(varchar(33), CAST(ModifiedDate AS datetime2(7)), 126), "
            "CONVERT(varbinary(8), ModifiedDate) "
            "FROM dbo.DateDriftExecute "
            "UNION ALL "
            "SELECT 'bulkcopy', "
            "CONVERT(varchar(33), CAST(ModifiedDate AS datetime2(7)), 126), "
            "CONVERT(varbinary(8), ModifiedDate) "
            "FROM dbo.DateDriftBulkcopy"
        )
        for row in cursor.fetchall():
            print(row)

Actual result

On both SQL Server 2022 and Fabric SQL database, I get:

('execute', '2025-05-16T16:33:33.1233333', b'\x00\x00\xb2\xdf\x01\x10\xe3!')
('bulkcopy', '2025-05-16T16:33:33.1200000', b'\x00\x00\xb2\xdf\x01\x10\xe3 ')

The only difference is the insertion path. The same Python datetime value is preserved by execute() and rounded down by bulkcopy().

Expected result

cursor.bulkcopy() should store the same SQL datetime value that cursor.execute() stores for the same Python datetime input.

Additional context

While validating a bulk-copy round-trip sample, I also saw the same effect on real source data:

• source SQL datetime: 2025-05-16T16:33:33.1233333
• fetched Python value: 2025-05-16 16:33:33.123000
• execute() round-trips back to 2025-05-16T16:33:33.1233333
• bulkcopy() stores 2025-05-16T16:33:33.1200000

That suggests the write-side conversion in bulkcopy() is the primary bug.

[github-actions]

Hi @dlevy-msft-sql, thank you for opening this issue!

Our team will review it shortly. We aim to triage all new issues within 24-48 hours and get back to you.

If you have additional information to share, please feel free to update the issue.

Thank you for your patience!

[saurabh500]

Duplicated the bug in microsoft/mssql-rs#20 since the fix will likely be in mssql-py-core