MSSQL DATETIMEOFFSET not roundtripable for timezones other than UTC

[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 64-bit
pyodbc: 4.0.35
OS: Windows 10
DB: SQL Server 2019 (15.0.4123.1)
driver: ODBC Driver 18 for SQL Server (2018.181.02.0
ODBC Trace: SQL.LOG

Issue

The timezone is getting lost somewhere when executing the query DECLARE @INPUT DATETIMEOFFSET=?; SELECT @INPUT AS [output] and passing in a datetime with a timezone other than UTC... I think pyodbc isn't sending it correctly, because the timezone comes back as UTC in the handle_datetimeoffset function. (see the test_round_trip_local test)

I'm not sure what's going on under the hood, but I think python datetimes should always be sent as DATETIMEOFFSET to MSSQL when they have the timezone set. No timezone should fallback to DATETIME2.. MSSSQL does implicit conversions of these to DATE/DATETIME so it should be backwards compatible.

import struct
import unittest
from datetime import datetime, timezone, timedelta
from zoneinfo import ZoneInfo

import pyodbc


cnxn_str = None
def handle_datetimeoffset(dto_value):
    # ref: https://github.com/mkleehammer/pyodbc/issues/134#issuecomment-281739794
    tup = struct.unpack("<6hI2h", dto_value)  # e.g., (2017, 3, 16, 10, 35, 18, 500000000, -6, 0)
    return datetime(tup[0], tup[1], tup[2], tup[3], tup[4], tup[5], tup[6] // 1000, timezone(timedelta(hours=tup[7], minutes=tup[8])))

class DateTimeOffsetRoundtripTest(unittest.TestCase):

    def test_round_trip_local(self):
        #THIS WORKS
        cnxn = pyodbc.connect(cnxn_str, autocommit=True)
        cnxn.add_output_converter(-155, handle_datetimeoffset)
        crsr = cnxn.cursor()
        expected = datetime(2022, 12, 27, 0, 0, 0, tzinfo=ZoneInfo("America/New_York"))
        crsr.execute("DECLARE @INPUT DATETIMEOFFSET=?; SELECT @INPUT AS [output]", [expected])
        actual = crsr.fetchone().output
        self.assertEqual(expected, actual)

    def test_round_trip_utc(self):
        # THIS FALSE
        cnxn = pyodbc.connect(cnxn_str, autocommit=True)
        cnxn.add_output_converter(-155, handle_datetimeoffset)
        crsr = cnxn.cursor()
        expected = datetime(2022, 12, 27, 0, 0, 0, tzinfo=ZoneInfo("UTC"))
        crsr.execute("DECLARE @INPUT DATETIMEOFFSET=?; SELECT @INPUT AS [output]", [expected])
        actual = crsr.fetchone().output
        self.assertEqual(expected, actual)
        self.assertEqual(actual.utcoffset(), expected.utcoffset())


if __name__ == '__main__':
    unittest.main()

[gordthompson]

Duplicate of #810

[hb2638]

Thx. I’ll close