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.
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.