Bug #24867 MyODBC failure when using a table with MySQL TIME data type
Submitted: 7 Dec 2006 0:26 Modified: 2 Mar 2007 18:31
Reporter: Erica Moss Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:3.51 OS:Windows (XP sp 2)
Assigned to: Mark Matthews CPU Architecture:Any

[7 Dec 2006 0:26] Erica Moss
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.
[15 Jan 2007 9:16] Tonci Grgin
Bug#25432 has been set to duplicate of this one.
[2 Mar 2007 18:31] Bogdan Degtyariov
Duplicated by bug#25846