pymssql is returning binary data for datetimeoffset

Question

I am using python 3.6 and pymssql to try and pull some data from a table. everything is fine except for datetimeoffset columns.

I have a table that has a column called DateCreated with a type of DateTimeOffset.

this column has a default set of sysdatetimeoffset()

When I use tsql, azure data studio, and the freetds utilities. they all return the expected value.

when I use python script that is essentially this

import pymssql


username = 'myusername'
pw = 'mypw'
server = 'myserver'
db = 'mydb'

c = pymssql.connect(server,username,pw,db)
cur = c.cursor()

cur.execute('select DateCreated from myTable where DateCreated and id = 2')

r = cur
for x in r:
    print (x)
    #print (struct.calcsize(data))
    #print (data.decode("utf-8"))
    break

the data that is expected would look something like this 2019-06-20 09:54:40.09550 -04:00

what i get is

b'\x00.,x\x82\x00\x00\x00p\xaa\x00\x00\x10\xff\x07\xe0'

great its binary i can unpack this.. wait no I cant.. I can use the pyodbc trick right RIGHT?!?! nope cant do that either that format string will not let me unpack the value.

So at this point I am stumped, what am I doing wrong, at the end of the day I want to know why the dates are coming back as binary instead of something I can manipulate.

Everything is telling me this should work.

Answer 1

I have a work around for this issue that got me the data I expected.

from datetime import datetime,timezone,timedelta
from dateutil.tz import tzoffset
import struct 

unpacked = struct.unpack('QIhH',x.DateCreated)
m = []
for tup in unpacked:
    m.append(tup)

print(m)

days= m[1]
microseconds = m[0] /10 if m[0] else 0

timezone = m[2]
tz = tzoffset('ANY',timezone * 60  )
print (tz)
my_date = datetime(*[1900,1,1,0,0,0],tzinfo=tz)
td = timedelta(days=days,minutes=m[2],microseconds=microseconds)
my_date += td
print(my_date)

The problem was the way the binary is interpreted by pymssql versus pyodbc. I could not use the same unpacking format string, the above string works and with some simple math it produces the expected value.