MySQL Bugs: #24195: Datetime conversion from Access does not work properly for dates before 1800

Bug #24195	Datetime conversion from Access does not work properly for dates before 1800
Submitted:	10 Nov 2006 15:12	Modified:	14 Feb 2007 14:37
Reporter:	Till Janetzki	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S3 (Non-critical)
Version:	1.1.5 rc	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
I tried to export an access database with dates between 1200 and 2000. As datatype in access i used datetime and didn´t change anything in the migration toolkit. For dates after around 1800 it works perfectly but for all other dates i get strange results.

The further away the are the bigger the difference gets. It´s maybe only a leap year thing or something but i couldn´t figure out what it is.

To test this I created a new table with one date and one string column and entered the same dates in both fields to see how it differs. Here´s what i got from the migration toolkit:

DROP TABLE IF EXISTS `foo`.`bar`;
CREATE TABLE `foo`.`bar` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `d_date` DATETIME NULL,
  `d_string` VARCHAR(50) NULL,
  PRIMARY KEY (`id`),
  INDEX `id` (`id`)
)

INSERT INTO `db5`.`foo`(`id`, `d_date`, `d_string`)
VALUES (1, '0999-07-02 00:00:00', '1.1.1000'),
  (2, '1399-10-07 00:00:00', '1.1.1400'),
  (3, '1449-10-19 00:00:00', '1.1.1450'),
  (4, '1499-10-31 00:00:00', '1.1.1500'),
  (5, '1549-11-12 00:00:00', '1.1.1550'),
  (6, '1599-11-24 00:00:00', '1.1.1600'),
  (7, '1649-12-07 00:00:00', '1.1.1650'),
  (8, '1699-12-19 00:00:00', '1.1.1700'),
  (9, '1749-12-31 00:00:00', '1.1.1750'),
  (10, '1800-01-01 00:00:00', '1.1.1800'),
  (11, '1850-01-01 00:00:00', '1.1.1850'),
  (12, '1900-01-01 00:00:00', '1.1.1900');

Sorry for my english and thanks for reading this.

How to repeat:
In Access create a table with a datetime column and try to export dates before 1800.

Thank you for the bug report. Could you please provide an *.mdb file which
presents the issue? Thanks in advance.

a simple Access Database with some dates

Attachment: dates.mdb (application/msaccess, text), 140.00 KiB.

I attached an Access file containing just one small table:

id  |   date   |   date_string
--------------------------------
1   | 1.1.1800 | 1.1.1800
2   | 1.1.1750 | 1.1.1750
3   | 1.1.1700 | 1.1.1700
...

When i run the migration toolkit i'll get the following out of it:

mysql> select * from foo;
+----+---------------------+-------------+
| id | date                | date_string |
+----+---------------------+-------------+
|  1 | 1800-01-01 00:00:00 | 1.1.1800    |
|  2 | 1749-12-31 00:00:00 | 1.1.1750    |
|  3 | 1699-12-19 00:00:00 | 1.1.1700    |
|  4 | 1649-12-07 00:00:00 | 1.1.1650    |
|  5 | 1199-08-19 00:00:00 | 1.1.1200    |
+----+---------------------+-------------+
5 rows in set (0.02 sec)

Thank you for the feedback.

mysql> select * from foo;
+----+---------------------+-------------+
| id | date                | date_string |
+----+---------------------+-------------+
|  1 | 1800-01-01 00:00:00 | 1.1.1800    |
|  2 | 1749-12-31 00:00:00 | 1.1.1750    |
|  3 | 1699-12-19 00:00:00 | 1.1.1700    |
|  4 | 1649-12-07 00:00:00 | 1.1.1650    |
|  5 | 1199-08-19 00:00:00 | 1.1.1200    |
+----+---------------------+-------------+
5 rows in set (0.00 sec)

-- ----------------------------------------------------------------------
-- SQL data bulk transfer script generated by the MySQL Migration Toolkit
-- ----------------------------------------------------------------------

-- Disable foreign key checks
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

INSERT INTO `dates`.`foo`(`id`, `date`, `date_string`)
VALUES (1, '1800-01-01 00:00:00', '1.1.1800'),
  (2, '1749-12-31 00:00:00', '1.1.1750'),
  (3, '1699-12-19 00:00:00', '1.1.1700'),
  (4, '1649-12-07 00:00:00', '1.1.1650'),
  (5, '1199-08-19 00:00:00', '1.1.1200');

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

-- End of script

Hi,

this seems to be a bug in the ODBC/JDBC connector. Tests against other databases do not suffer from this issue.

As I am not aware of any possible automated workaround for this bug I have to set it to WillNotFix since we cannot change SUN's ODBC/JDBC connector.

A manual workaround would be to add a new text column for each MS Access table and update this column to contain the MS Access date in text format, then convert the text back to a MySQL DATETIME column.

Mike