[python-win32] python win32com.client; reverse engineer sql server timestamp

[Tim Roberts]

On Dec 17, 2018, at 5:20 PM, Anrik Drenth <drentha at gmail.com> wrote:
> 
> I'm looking for the mapping table / logic that will render sql server timestamp values as win32com.client does.

OK, then why did you not tell us what the win32com.client does?


> I'm currently  importing a text file into Access. The text file contains a column with a timestamp value which has been extracted from sql server.  The values for the (Access String 50) column look something like "0x000000000189CF01".

That's a 32-bit integer.  You need to be storing this as a 32-bit integer, not as a string.  Or, best of all, as an Access date/time datatype.  Exactly which data type in the SQL Server column?  How was the text file generated?  In virtually every case, SQL Server will export date and time columns as date time strings (that is, "2018-12-17".  How did you get a hex representation?
 

> Using VBA I want to convert these to the exact same value that win32com creates.
> 
> In python (sample code below) the timestamp value is stored as an 8 byte, bytearray.  Which then gets interpreted by Access.  

Which version of Python are you using?  You are taking binary data and shoving it through a Unicode-to-ASCII conversion.  That produces garbage.  That's not a string.  It's an integer.


> Below is a sample of how the sql server timestamp value is stored in Access:
> 
> SQL Server (Timestamp) | Access (Text 50) 0x000000000189CF01  |  003F49  0x000000000189D001  |  003F69  0x0000000001B54DFF  |  003F6D  0x0000000001F74701   | 003F4E  0x000000000189C003   | 003F70  
> So it tries to do a mapping, sometimes 1 to 1, sometimes not as page 5 of the below link demonstrates 

Right.  You're producing garbage.  The "3F" in there is the "?" character, which means "this character does not exist in the current 8-bit code page".  It's not a Unicode string, don't try to treat it like one.
— 
Tim Roberts, timr at probo.com
Providenza & Boekelheide, Inc.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-win32/attachments/20181217/dba238f6/attachment.html>