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.

Can you add more details about how you are saving the dates? — ParthS007
– ParthS007, Commented Jun 20, 2019 at 20:22
they are being auto generated by sysdatetimeoffset as a default updating question — ttallierchio
– ttallierchio, Commented Jun 20, 2019 at 20:24
If there is anything additional i could provide please let me know — ttallierchio
– ttallierchio, Commented Jun 20, 2019 at 20:43

ttallierchio · Accepted Answer · 2019-06-21 20:16:01Z

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.

edited Jun 21, 2019 at 20:16

answered Jun 21, 2019 at 15:40

ttallierchio

4607 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

lukassliacky Over a year ago

In my case was better solution: stackoverflow.com/a/52451469/2278511 + install ODBC Driver from: learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/…

ttallierchio Over a year ago

@lukassliacky that is ultimately what we moved towards for this issue. there was a disagreement over drivers, and we had to determine which one had the most support/best through put etc. this solution just so every one is aware should be depreciated away, as there is no reason to use pymssql anymore. github.com/pymssql/pymssql/issues/668

Collectives™ on Stack Overflow

pymssql is returning binary data for datetimeoffset

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related