time column show weird date values in MS Access

I have a MySql table with a column of type "time". When I link the table=
into an Access database (or import it into the Access database) using=
MySQL ODBC 3.51 Driver, the content of the time column shows up weird date=
values in Access:

value in MySql table: 18:00:00
=3D> value in Access:

value in MySql table: 16:30:05
=3D> value in Access: 05.06.2018

??? What the...? Somebody got a hint for me? Is this a bug?

Andi


Here are the screen dumps to show what exactly happened:

mysql> select * from test;
+----+----------+
| id | t |
+----+----------+
| 1 | 18:00:00 |
| 2 | 16:30:05 |
+----+----------+
2 rows in set (0.00 sec)

mysql> show create table test;
+-------+--------------------------------------------------- ----------------=
-------------------------------+
| Table | Create Table=
=
|
+-------+--------------------------------------------------- ----------------=
-------------------------------+
| test | CREATE TABLE `test` (
`id` int(11) NOT NULL default '0',
`t` time default NULL
) TYPE=3DMyISAM |
+-------+--------------------------------------------------- ----------------=
-------------------------------+
1 row in set (0.00 sec)


In Access:

id t
---------------------------
1
2 05.06.2018



--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dgcdmo-myodbc@m.gmane.o rg

Re: time column show weird date values in MS Access

------------=_1105921114-21294-91
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Andreas Meier wrote:

>I have a MySql table with a column of type "time". When I link the table into an Access database (or import it into the Access database) using MySQL ODBC 3.51 Driver, the content of the time column shows up weird date values in Access:
>
>value in MySql table: 18:00:00
> => value in Access:
>
>value in MySql table: 16:30:05
> => value in Access: 05.06.2018
>
>??? What the...? Somebody got a hint for me? Is this a bug?
>
>Andi
>
>
>Here are the screen dumps to show what exactly happened:
>
>mysql> select * from test;
>+----+----------+
>| id | t |
>+----+----------+
>| 1 | 18:00:00 |
>| 2 | 16:30:05 |
>+----+----------+
>2 rows in set (0.00 sec)
>
>mysql> show create table test;
>+-------+-------------------------------------------------- ------------------------------------------------+
>| Table | Create Table |
>+-------+-------------------------------------------------- ------------------------------------------------+
>| test | CREATE TABLE `test` (
> `id` int(11) NOT NULL default '0',
> `t` time default NULL
>) TYPE=MyISAM |
>+-------+-------------------------------------------------- ------------------------------------------------+
>1 row in set (0.00 sec)
>
>
>In Access:
>
>id t
>---------------------------
>1
>2 05.06.2018
>
>
Yes MS Access sucks.
In this case, it sucks because it doesn't understand the column type 'time'.
The easiest workaround I've found is to use a datetime type instead, and
populate the field with values starting with '1899-12-30' ( which Access
takes as it's cue that it's dealing with a time field ... strange ... ).
eg your time of 6pm ( 18:00:00 ) would be entered as:

1899-12-30 18:00:00

You can populate your table with something like:

update MyTable
set New_DateTimeField = concat('1899-12-30 ', Old_TimeField)

Of course you'll have to create the New_DateTimeField first. And when
you've finished, relink your table in Access and test your data. And
when you're satisfied, delete the old time field, and relink your table
again.

Dan

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au


------------=_1105921114-21294-91
Content-Type: text/plain; charset=us-ascii

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org
------------=_1105921114-21294-91--