Brock - is this a 64bit app running on OS X? I know when using the Xojo ODBC driver to connect to a FileMaker Pro database, theres some issues with flipped bytes in the raw recordset data returned from the Xojo ODBC plugin:
[code]ODBC recordset date in 32 bit E2 07 09 00 14 00 10 00 09 00 2A 00 00 00 00 00
ODBC recordset date in 64 bit 07 E2 00 09 00 14 00 10 00 09 00 2A 00 00 00 00[/code]
(this is from looking at the values in the debugger)
I don’t really have control over this as I’m using Xojo’s built-in RecordSet class. IdxField returns a dateTime variant and the date is wrong.
The only way I’ve been able to work around it is to convert all the date columns in my queries to use “Format” operator so it comes back as a string. But doing so is a pain cause I can’t do things like “Select *”
[quote=417529:@Brock Nash]
I don’t really have control over this as I’m using Xojo’s built-in RecordSet class. IdxField returns a dateTime variant and the date is wrong.[/quote]
You may have more control than you believe… . If the msSQL datatype is inconsistent with what Xojo thinks is dateTime then you will get a wrong answer. So what msSQL datatype is the field in question?
FYI (my opinion) Date and Time values should ALWAYS be stored in SQLDateTime format (as strings) to maintain the most compatiblity between platforms, database engines and future proofing. YYYYMMDD HH:MM:SS TZ
This way there is no question about if the database has one interpetation and Xojo (or whatever) has another.
Brock - I got those values from the built-in recordset class; it returns DatabaseField objects which have a “Value” property which returns a variant.
I think the issue is the date data has the wrong byte order (its little-endian vs. big-endian or vice versa); if this is the case, then you could probably extend DatabaseField to have a “FixedDate” method which puts the native value in a memory block, fixes its byte-order, and then converts the corrected data back into a date object and returns it.
Then youd call
someDate = rs.field("my_date_field").FixedDate
vs.
someDate = rs.field("my_date_field").DateValue
I havent had the time to look into this issue more deeply, and Im not and expert at working with binary data so this is all “best guess”.
I know Dave is pretty proficient with binary data operations, so he may be able to verify or torpedo my conjecture about this being a byte order issue with the returned data from Xojos ODBC plugin.
Anthony … what you pointed out looks like a mismatch in the ENDIAN property of how the data is stored (ie. the order of bytes). If you look at your example, they both contain the same bytes but in a different order. Another reason why I support the use of SQLDATE format