Datetime Being Wrongly Converted To Timestamp

[Silkatron]

Hi

I'm using SQLyog v8.71

Import external data function with source as SQL Server 2005 and destination as MySQL 5.1.39

Migrating approx 70 tables

For some reason it is converting MSSQL datetime fields to MySQL timestamp fields

Surely a bug?? Any suggestions?

[peterlaursen]

There is an option to map source columns' datatypes to target columns' datatypes as you want (look behind the 'advanced' button).

I do not remember full details now, but I remember we discussed and decided to do like this some years ago – as far as I remember because neither TIMESTAMP nor DATETIME have the same properties in MySQL and SQL Server. We concluded that TIMESTAMP would most often be most appropriate in this situation (and the way a DATETIME is used in SQL Server is often how a TIMESTAMP is used in MySQL)

Another option is to map to a string (like varchar(20)) and then ALTER TABLE after migration. DATE/TIME datatypes are tricky to handle across different database server systems, and don't expect it to be perfect in every situation without user intervention.

[peterlaursen]

Now I think I remember that ODBC (and thus the ODBC driver for SQL Server) does not expose the difference between DATETIME and TIMESTAMP. For ODBC they are the same.

But we will need to verify this.

[Silkatron]

Thanks for your response

I ended up manually specifying datetime fields for each instance

The option you mentioned in the advanced tab doesn't exist ( at least in the version I'm using ) and could be useful, as the migration was successful after the manual changes

Thanks again

[Author]

Posts