pyodbc: ODBC data type -155 is not supported.

If it helps, I just wrote an output converter function for this.

The binary format is this: struct.Struct("<6hI2h") …which unpacks 20 bytes of data into a tuple of 9 values: year, month, day, hours, minutes, seconds, nanoseconds, offset_hours, offset_minutes …which can be used to construct a datetime object.

If the timezone offset is negative, both offset_hours and offset_minutes will be negative.

You’ll need a tzinfo implementation if you want your datetimes to be timezone aware - FixedOffsetTimezone from psycopg2.tz does the job. Also remember that datetime’s constructor accepts microseconds, not nanoseconds:

total_offset_minutes = offset_hours * 60 + offset_minutes result = datetime.datetime(year, month, day, hours, minutes, seconds, nanoseconds // 1000, tzinfo=FixedOffsetTimezone(total_offset_minutes))

My understanding is that exactly what pyodbc supports will depend on exactly what constants are defined in headers like sql.h, sqlext.h, or msodbcsql.h when it’s built and installed. That could explain why it works on one system and not another.

In case this is useful to anyone else, here is a short write-up of an investigation into this that I did a while ago while trying to fix this in Django. (I ended up with a janky monkeypatch that I’m not going to recommend using.) I’m not entirely sure all of this is correct at this point, either, and I’d be happily corrected on any of it.

---

SQL type -155 is SQL_SS_TIMESTAMPOFFSET, which you can find documented:

https://docs.microsoft.com/en-us/sql/relational-databases/native-client-odbc-date-time/data-type-support-for-odbc-date-and-time-improvements?view=sql-server-2017

Since this is a SQL-Server-specific extension to ODBC, it’s defined only in msodbcsql.h (e.g. /opt/microsoft/msodbcsql17/include/msodbcsql.h), and not in /usr/include/sql.h or sqlext.h as provided by unixodbc-dev.

If you grab the pyodbc source:

https://files.pythonhosted.org/packages/a0/14/d23278740dd64aa47eea396c4caf5a1b464dacaf339dca21b9afbc973e4b/pyodbc-4.0.22.tar.gz

…you can see in src/pydobc.h that it’s getting its values for those constants like SQL_SS_TIMESTAMPOFFSET from sql.h and sqlext.h, and it uses those (e.g. in src/getdata.cpp) to figure out how to process data.

Those in turn get baked into site-packages/sql_server/pyodbc/introspection.py when you install the package.

So to fix this “correctly”, we would need to patch pyodbc to make it include Microsoft’s custom extensions. This seems like an unlikely patch to make it upstream.

So why does this work with older versions of Django/Python/pyodbc? I’m not sure, but I think there might have previously been some kind of automatic or catch-all conversion to another datetime type. I haven’t had a chance to go through the commit history and look for that.

SQLAlchemy here .

How we doing on this? I’m getting hit on this now. I’m using the Microsoft drivers on linux.

@vdurante - I’ve been working on an enhancement to SQLAlchemy to add datetimeoffset support to the mssql+pyodbc dialect. I’m hoping that it will be included in the next release. See this comment for (IMO) the underlying reason why support for datetimeoffset is not baked into pyodbc itself.

Further to comment from @v-chojas:

Situation described by @MichaelE919 is almost certainly that the Windows machine was using the ancient “SQL Server” driver (shipped with Windows) while the Linux machine was using a newer “ODBC Driver xx for SQL Server”. The former returns the value as a string, while the latter returns a type -155 value which requires an output converter function to interpret the results. The issue is also documented in the wiki here.

Which ODBC driver is being used is also important, because the server will not advertise the newer types to those older drivers which do not support them:

https://docs.microsoft.com/en-us/sql/relational-databases/native-client-odbc-date-time/enhanced-date-and-time-type-behavior-with-previous-sql-server-versions-odbc

Client applications that were compiled using a version of SQL Server Native Client prior to SQL Server 2008 see the new date/time types as nvarchar columns.