I am querying a SQL Server database that has a column of type datetimeoffset
. I am using 'pyodbc' and SQL Server 2017. The datetime is being returned as strings as follows:
"b'\xe3\x07\n\x00\x0e\x00\x12\x00\x03\x00\x05\x00@\xe1\x9d\x18\x00\x00\x00\x00'"
Pandas doesn't recognize it as a timestamp and I have tried using Python 3 'struct' module to unpack it like this:
import struct
raw = 'b\xe3\x07\n\x00\x0e\x00\x12\x00\x03\x00\x05\x00@\xe1\x9d\x18\x00\x00\x00\x00'
unpacked, = struct.unpack('<Q', raw)
That errors out because 'raw' is a string. If I enter the string directly as an argument in 'unpack' it errors out because of wrong number of bytes.
How do I convert the column values to pandas datetime?
Additional Note: This site indicates that the SQL Server uses a particular type that pyodbc doesn't handle natively as suggested by mostert. That said, they seem to have no problem retrieving a human-readable value.
[SOLVED] So the solution at this site does work. TIL: when adding the converter you need to get the type as an integer, in this case '-155'. This site has the integer codes for some other types
So the solution at this site does work. TIL: when adding the converter you need to get the type as an integer, in this case '-155'. This site has the integer codes for some other types