Work Item / Issue Reference

AB#34944

GitHub Issue: #<ISSUE_NUMBER>

---

Summary

This pull request adds comprehensive support for the SQL Server DATETIMEOFFSET type in the driver, including full round-trip handling of timezone-aware Python datetime objects. The changes span the C++ bindings, Python type mapping, and test coverage to ensure correct reading and writing of DATETIMEOFFSET values, as well as validation for timezone information.

DATETIMEOFFSET support in C++ bindings:

• Defined new constants and a DateTimeOffset struct in ddbc_bindings.cpp to represent and handle the SQL Server DATETIMEOFFSET type and its C type mapping (SQL_SS_TIMESTAMPOFFSET, SQL_C_SS_TIMESTAMPOFFSET). [1] [2]
• Implemented logic in parameter binding to convert Python timezone-aware datetime objects into the DateTimeOffset struct, including extracting and validating timezone offsets. Naive datetimes are rejected for this type.
• Added logic to fetch DATETIMEOFFSET values from the database and convert them back into Python datetime objects with the correct timezone information.

Python type mapping and constants:

• Added SQL_DATETIMEOFFSET and SQL_C_SS_TIMESTAMPOFFSET to the driver's constants in constants.py.
• Updated the cursor's type mapping logic to use DATETIMEOFFSET for timezone-aware datetime objects, and TIMESTAMP for naive ones.

Testing improvements:

• Introduced a new test, test_datetimeoffset_read_write, which verifies correct round-trip handling of DATETIMEOFFSET values, including various timezone offsets and microsecond precision, and ensures that naive datetimes are rejected.
• Added necessary imports for timezone handling in the test module.

These changes ensure that the driver now robustly supports SQL Server's DATETIMEOFFSET type, providing accurate and safe handling of timezone-aware datetimes between Python and the database.