0

How to convert a MSSQL time datatype to python time or string? The column in MSSQL server is defined as time(7).

On Windows7, I am using Python3/adodbapi, and column returned as bytes:

sql = 'SELECT top 1 * FROM table1'
with adodbapi.connect('srv') as cn:
    with cn.cursor() as cur:
        cur.execute(sql)
        for row in cur:
            print(type(row['col1']))
            print(row['col1'])
            for c in row['col1']: print(c)

>>> <class 'bytes'>
>>> b'\t\x00\x1e\x00\x00\x00\x00\x00\x00\x00\x00\x00'
>>> 9
    0
    30
    0
    0
    0
    0
    0
    0
    0
    0
    0

How do I convert the python bytes back to time or string?

On linux, I am using Python3/pymssql, and column returned as string, which I can parse into time.

>>> <class 'str'>
>>> 09:30:00.0000000
fivelements
  • 1,487
  • 2
  • 19
  • 32
  • Perhaps this answer can help you: http://stackoverflow.com/questions/20024490/how-to-split-a-byte-string-into-separate-bytes-in-python#answer-20024532 – kylieCatt Jun 16 '15 at 14:16
  • I end up tweak the sql statement with convert(smalldatetime, col1) as time1 – fivelements Jun 16 '15 at 16:40
  • If you ahve managed to solve your problem on your own please detail what you did as an answer so future readers can benefit from it. You can also accept your own answer. – kylieCatt Jun 16 '15 at 16:42

1 Answers1

0

The workaround I have in place is to tweak the sql statement so that the tsql convert the time() column explicitely:

sql = 'SELECT *, convert(smalldatetime, col1) as ncol1  FROM table1'

On both windows(adodbapi) and linux(pymssql), I am getting consistent python datetime object for 'ncol1' now.

Update: It looks like a db driver issue. After switching to pymssql on Windows, I find that the time(7) column in the database is also returned as string, the same as the one on Linux. (So both Linux/windows with pymssql return String for the time column, which is consistent.) For some reason, adodbapi returned bytearray.

fivelements
  • 1,487
  • 2
  • 19
  • 32