Handle Type -155

[lstorme]

Describe the bug

Need to handle MsSQL type -155

E RuntimeError: Unsupported data type for column - 0x7ffee88bb7c0, Type - -155, column ID - 7

self = <mssql_python.cursor.Cursor object at 0x7f31b52f7980>

    def fetchone(self) -> Union[None, Row]:
        """
        Fetch the next row of a query result set.

        Returns:
            Single Row object or None if no more data is available.
        """
        self._check_closed()  # Check if the cursor is closed

        # Fetch raw data
        row_data = []
>       ret = ddbc_bindings.DDBCSQLFetchOne(self.hstmt, row_data)
E       RuntimeError: Unsupported data type for column - 0x7ffee88bb7c0, Type - -155, column ID - 7

../../../../py312/lib/python3.12/site-packages/mssql_python/cursor.py:724: RuntimeError

To reproduce

Try to select from table containing datetimeoffset or datetime

Expected behavior

Return record

Further technical details

Python version: 3.12
SQL Server version: (e.g. SQL Server 2022)
Operating system: RedHat 8

Additional context
table:
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[RecCreateDateTime] [datetime] default getutcdate(),
[FileID] varchar NOT NULL, -- merchant affected by the task
[task] varchar NOT NULL, -- type of the task BATCH, S3, ...
[dateTimeUTC] [datetimeoffset] NULL, -- date and time of the task
[guid] [uniqueidentifier],

[gargsaumya]

Thanks for reporting this! The error occurs because datetimeoffset (ODBC type -155) isn’t supported yet in mssql-python. We’re actively working on adding support, and it will be available shortly.

We really appreciate you trying out the driver, please keep sharing your feedback and inputs!

[gargsaumya]

Hi @lstorme,
Wanted to let you know that the support for this ODBC type is now added and the related pull request (#236 ) has been merged. You can expect this to be included in the next release, which is planned for Friday.

Once the release is out, please give it a try and let us know if it works as expected. Your feedback is really valuable.
Thanks again for contributing to improving mssql-python, and please keep raising any issues or suggestions you encounter. :)

[gargsaumya]

We’ve included this now in our 0.12.0 release. Please give it a try and let us know if it works as expected. Thank you for your support!

@gargsaumya I have the same issue as the OP. The issue is not fixed in 0.12.0. The linked PR seems to enable support for UUID, not datetimeoffset. Looks like #260 may enable datetimeoffset suuport when merged.

[gargsaumya]

Right @HakonEngerStatkraft , apologies for the confusion, mistakenly linked the wrong PR here. I’ll update here again, once #260 PR is merged.

[gargsaumya]

@HakonEngerStatkraft , @lstorme - We’ve included the support for this data type in our 0.13.0 release. Please give it a try and let us know if it works as expected. Thank you for your support!

@gargsaumya Thank you for this. Unfortunately, using version 0.13.0, the time zone information is lost when reading a datetimeoffset column from a table, since the datetime is converted to UTC. We need to read the time zone also (which may be different for each row in the table for us), so this implementation does not fulfill our needs. We can read the datetime and timezone as a string using convert(nvarchar(35), column, 121).

[gargsaumya]

Hi @HakonEngerStatkraft ,
Thanks for trying this out and sharing the behavior. Could you please share a brief snippet or example of your use case as well? This will help us ensure that the implementation supports the real user scenario correctly.

Thanks again for taking the time to test and provide input!

This partial ipython log shows the behavior where the time zone information is lost when reading as a datetime (note that tzinfo=datetime.timezone.utc in the second returned row) but retained when converted to a string (note +0100 in the first return value):

In [2]: import mssql_python

In [3]: conn = mssql_python.connect(SQL_CONNECTION_STRING)

In [4]: cur = conn.cursor()

In [5]: cur.execute("SELECT TOP 1 convert(nvarchar(35), Systime, 121) FROM cdp.EventLogs WHERE Recno=162243");cur.fetchall()
Out[5]: [('2025-05-14 12:35:52.5010000 +01:00',)]

In [6]: cur.execute("SELECT TOP 1 Systime FROM cdp.EventLogs WHERE Recno=162243");cur.fetchall()
Out[6]: [(datetime.datetime(2025, 5, 14, 11, 35, 52, 501000, tzinfo=datetime.timezone.utc),)]

Not sure, but I suspect the behavior stems from calling .astimezone(datetime.timezone.utc) here: https://github.com/microsoft/mssql-python/pull/260/files#diff-dde2297345718ec449a14e7dff91b7bb2342b008ecc071f562233646d71144a1R2711