Description:
Values in MySQL Time columns are displaying the wrong value when viewed in a linked table
in MS Access 2003. A value like 18:02:38 when view in Access is 3/10/2018. Looking at
the schema of the linked table within Access it appears that the type is interpreted as
Date/Time. However there is only data in the date portion of that structure. If the
format of the field is changed in Access from General to one of the Time formats then the
time displays as 00:00 or 12AM.
I looked at the type reported within a VB ADO type tester and these are the values it
sees for this field.
Constant: adDBTime
Description: Indicates a time value (hhmmss)
OLE Type: DBTYPE_DBTIME
Defined Sz: 6 Bytes
This is from a driver manager trace:
MSACCESS 1148-89c ENTER SQLGetData
HSTMT 07EF26C0
UWORD 2
SWORD 11 <SQL_C_TIMESTAMP>
PTR <unknown type>
SQLLEN 512
SQLLEN * 0x0013CC44
MSACCESS 1148-89c EXIT SQLGetData with return code 0 (SQL_SUCCESS)
HSTMT 07EF26C0
UWORD 2
SWORD 11 <SQL_C_TIMESTAMP>
PTR <unknown type>
SQLLEN 512
SQLLEN * 0x0013CC44 (16)
How to repeat:
CREATE TABLE testtable (c1 INT AUTO_INCREMENT PRIMARY KEY,
c2 TIME, c3 TIMESTAMP);
INSERT INTO testtable (c2) VALUES (now());
mysql> select * from testtable;
+----+----------+---------------------+
| c1 | c2 | c3 |
+----+----------+---------------------+
| 1 | 18:02:38 | 2006-12-06 18:02:38 |
+----+----------+---------------------+
Link a new table from access to this new MySQL table and open it.
It will display a date instead of a time.
Description: Values in MySQL Time columns are displaying the wrong value when viewed in a linked table in MS Access 2003. A value like 18:02:38 when view in Access is 3/10/2018. Looking at the schema of the linked table within Access it appears that the type is interpreted as Date/Time. However there is only data in the date portion of that structure. If the format of the field is changed in Access from General to one of the Time formats then the time displays as 00:00 or 12AM. I looked at the type reported within a VB ADO type tester and these are the values it sees for this field. Constant: adDBTime Description: Indicates a time value (hhmmss) OLE Type: DBTYPE_DBTIME Defined Sz: 6 Bytes This is from a driver manager trace: MSACCESS 1148-89c ENTER SQLGetData HSTMT 07EF26C0 UWORD 2 SWORD 11 <SQL_C_TIMESTAMP> PTR <unknown type> SQLLEN 512 SQLLEN * 0x0013CC44 MSACCESS 1148-89c EXIT SQLGetData with return code 0 (SQL_SUCCESS) HSTMT 07EF26C0 UWORD 2 SWORD 11 <SQL_C_TIMESTAMP> PTR <unknown type> SQLLEN 512 SQLLEN * 0x0013CC44 (16) How to repeat: CREATE TABLE testtable (c1 INT AUTO_INCREMENT PRIMARY KEY, c2 TIME, c3 TIMESTAMP); INSERT INTO testtable (c2) VALUES (now()); mysql> select * from testtable; +----+----------+---------------------+ | c1 | c2 | c3 | +----+----------+---------------------+ | 1 | 18:02:38 | 2006-12-06 18:02:38 | +----+----------+---------------------+ Link a new table from access to this new MySQL table and open it. It will display a date instead of a time.