ODBC connection to MySQL Time Format Problem

[OPMCoordinator]

OK, I give up.

I have just finished 4 hours searching and searching.

I have access to a MySQL database that has a time field. I wish to use the DTS packages in SQL Server 2000 to aquire the data to the SQL Server.

I can connect/see it using an ODBC connection, both with access 2k and the DTS bizzo on SQL server.

The time data is being seen by the microsoft end as a date format with way out values.

As MySQL has a very obvious different way of handling date/time (2 data types - one date and the other time), there must be an obvious solution to be able to use this data in access / SQL server.

Hiowever, I cant seem to find it.

damn - fire alarm must go - please help if you understand this - i will ammend later

mike

 

[namliam]

Try pulling the date/time things as strings/numbers by doing something like:

Format (DateField,'yyyymmdd')

Greetz & GL with the fire (drill i hope)

 

[Gary_England]

Mike. I had exactly the same issue.

All Fields with a Time format returned only dates. No matter how you try format this it isn't correct.

After a few hours research I think I've found the problem.

The ODBC Driver MySQL 3.51.10.00 dosn't convert the MySQL Times correctly. It simply returns Date values.

If you go back to the

ODBC Driver MySQL ODBC 2.50.39.00 and use this I believe you will find the correct times being produced. This worked for me.

I hope this helps. I beleive MySQL are aware of this issue.

Gary.