ID #1108

I get ZERO's when importing DATE and TIME types


It is an issue with the data type. DATE and TIME data types - and their implementations - differ quite a lot across database systems. And things do not get less complicated as there is an ODBC-layer 'in-between' the source and the target. It simply is not possible to take every detail of every implementation into account automatically.

Note that

  • By default SQLyog Import External Data Tool map such types to a MySQL TIMESTAMP type. We believe that that is most often what most users want and need. But In 'non-strict' mode an invalid value for a TIMESTAMP is silently converted to a ZERO-date by the MySQL server. So this is the reason for the occurring ZERO's
  • After import you may want to adjust the column definition using the 'on update CURRENT_TIMESTAMP' clause on MySQL versions that support it.
  • The MySQL TIMESTAMP type does not support dates like 1919-01-01 for instance. MySQL TIMESTAMP starts at '1970-01-01 00:00:00'. You will need to use a DATETIME (or a DATE if there is not 'time of day' involved) for such data. You can set the mapping behind the 'map' button for each column.
  • There are 'range' restrictions with DATE and DATETIME too - but they are rarely of practical importance! However with 'archeological data' you may need to construct your own date format using more columns of simple types - or simply use an signed integer for instance.

Also refer to the MySQL docs at:
(please note that the implementation of those types differ slightly with different MySQL versions!)


Tags: -

Related entries:

You can comment this FAQ