SQL/Microsoft ADO (Advanced Data Objects) VT_DATE quirk

[jj2007]

When working on my spreadsheet editor, I stumbled over a nice little quirk of Microsoft ADO (Advanced Data Objects). This is a typical sequence to get a table from e.g. an Excel file:

Code Select Expand

CoInvoke pFlds, Fields.get_Item, vIndex, addr pFld
CoInvoke pFld, Field.get_Name, addr bstrTmp
CoInvoke pFld, Field.get_Value, addr v3
The variable v3 is a VARIANT. Field.get_Value copies the content of a cell into a buffer.

That works fine, most of the time, and you'll find an Ole$ alias BSTR in your buffer, i.e. a UTF-16 representation of the cell content.

Now the quirk: if your spreadsheet was saved by good ol' M$ Excel, and there are cells in DATE format, such as 05/07/2011, then M$ ADO will not hesitate to copy a Unicode string into your buffer. And guess what? It does look like a date!

Only that it is roughly 600 years off

Lovely. So I had to code a little special handler just in case OpenAdo() encounters a cell with the VT_DATE format:

Code Select Expand

CoInvoke pFld, Field.get_Value, addr v3 ; no jfne: may have an error for a single cell
movzx ecx, v3.vt
.if eax==80040e21h && ecx==VT_BSTR ; highly misleading M$ "help"; in reality, this is a rare
mov v3.bstrVal, Ole$("#error#") ; "Number stored as text" error when cell has "General" property
.endif ; but gets treated as number; see Vermont in Superstore data.xls
.if ecx!=VT_BSTR
.if ecx<=VT_NULL ; VT_EMPTY=0, VT_NULL=1
and v3.bstrVal, 0 ; make it an empty string, see adoNull
.elseif ecx==VT_DATE ; M$ on Excel dates
fld v3.llVal ; Excel date format serial number on FPU
ExternDef nsPerDay:REAL4 ; ok until x.x.2500
fmul nsPerDay ; FP4(864000000000.0) ; one day
fadd FP8(94353704400000000.0) ; 1.1.1900-2 days
push eax
push eax
fistp qword ptr [esp] ; Eric Lippert: The OLE automation date format is a floating point
movlps xmm0, qword ptr [esp] ; value, counting days since midnight 30 December 1899.
pop edx ; Hours and minutes are represented as fractional days.
pop edx
void wfDate$(xmm0)
mov v3.bstrVal, eax
.else
invoke VariantChangeType, addr v3, addr v3, 13, VT_BSTR ; M$ Learn
.endif
.endif
MasmBasic 28.4.24 has it right.