FIX: Align date/time type code mappings with ODBC 18 driver source (#352) by dlevy-msft-sql · Pull Request #355

FIX: Align date/time type code mappings with ODBC 18 driver source (#352) by dlevy-msft-sql · Pull Request #355 · microsoft/mssql-python

AI review requested due to automatic review settings

December 1, 2025 17:19

dlevy-msft-sql added a commit to dlevy-msft-sql/mssql-python that referenced this pull request

Jan 20, 2026

- Add SQL_SS_XML (-152) constant to Python constants.py
  (was incorrectly using SQL_XML = 241)
- Add SQL_SS_TIME2 (-154) constant for SQL Server TIME(n) type
- Update cursor type map to use SQL_SS_XML and add SQL_SS_TIME2 mapping
- Add sync comment in C++ to prevent future constant drift

Constants verified against Microsoft Learn ODBC documentation:
- SQL_SS_TIME2: -154 (SQLNCLI.h)
- SQL_SS_TIMESTAMPOFFSET: -155 (SQLNCLI.h)
- SQL_SS_XML: -152 (SQL Server ODBC driver)
- SQL_SS_UDT: -151 (SQL Server ODBC driver)

Addresses Copilot review feedback on PR microsoft#355

Root cause: _map_data_type only had ODBC 2.x constants (SQL_DATE=9,
SQL_TIME=10, SQL_TIMESTAMP=11) but the ODBC 18 driver reports ODBC 3.x
codes via SQLDescribeCol. Date columns returned SQL_TYPE_DATE(91) which
fell through to str default, causing polars ComputeError.

Verified against ODBC 18 driver source (sqlcmisc.cpp rgbSRV2SQLTYPE[]
and sqlcdesc.cpp SQL_DESC_CONCISE_TYPE handling):

constants.py:
- Add SQL_SS_TIME2(-154), SQL_SS_XML(-152), SQL_C_SS_TIME2(0x4000)

cursor.py _map_data_type:
- Replace ODBC 2.x entries with driver-verified ODBC 3.x codes
- SQL_TYPE_DATE(91) -> datetime.date
- SQL_TYPE_TIMESTAMP(93) -> datetime.datetime
- SQL_SS_TIME2(-154) -> datetime.time
- SQL_DATETIMEOFFSET(-155) -> datetime.datetime
- SQL_SS_XML(-152) -> str
- Add missing types: SQL_LONGVARCHAR, SQL_WLONGVARCHAR, SQL_REAL

cursor.py _get_c_type_for_sql_type:
- SQL_TYPE_DATE -> SQL_C_TYPE_DATE (was SQL_DATE)
- SQL_SS_TIME2 -> SQL_C_SS_TIME2 (was SQL_TIME)
- SQL_TYPE_TIMESTAMP -> SQL_C_TYPE_TIMESTAMP (was SQL_TIMESTAMP)
- Add SQL_DATETIMEOFFSET -> SQL_C_SS_TIMESTAMPOFFSET

Tests:
- 14 tests: cursor.description type_code verification (6 date/time
  types + isclass check), polars integration (4), pandas integration (3)

Closes microsoft#352