"Invalid date literal detected" when reading decimal
Hi everyone, I'm attempting a migration from MSSQL to MySQL and keep coming across a "Invalid date literal detected" error when reading certain decimal columns. I'm running the following (all up-to-date as of posting):
Windows 7
MSSQL 2008 R2
MySQL 5.6.21 Community
Workbench 6.2.3
I've attempted to re-run the migration a number of times with the same rows/tables causing the same issues. For instance with the following 'Times' table:
INSERT INTO `GenshenHR2`.`Times` (`TimesID`, `TimesheetID`, `StaffID`, `StoreID`, `JobID`, `Hours`, `Holiday`, `Overtime`, `Unpaid`) VALUES (1,1,1,1,1,'97.0','.0','.0','.0'),(2,1,2,1,2,'152','.0','.0','.0'),(3,1,3,1,3,'152','.0','.0','.0'),(4,1,4,1,4,'152','.0','.0','.0'),(5,6,5,6,6,'.0','.0','.0','.0'),(6,6,8,6,9,'.0','.0','.0','.0'),(7,6,9,6,10,'80.0','8.0','8.0','.0'),(8,6,10,6,11,'.0','.0','.0','.0'),(9,6,11,6,12,'.0','.0','.0','.0'),(10,6,12,6,13,'.0','.0','.0','.0'),(11,6,13,6,14,'.0','.0','.0','.0'),(12,6,14,6,15,'.0','.0','.0','.0'),(13,6,15,6,16,'.0','.0','.0','.0'),(14,6,16,6,17,'.0','.0','.0','.0'),(15,6,17,6,18,'.0','.0','.0','.0'),(16,6,18,6,19,'.0','.0','.0','.0'),(17,6,19,6,20,'.0','.0','.0','.0'),(18,6,20,6,21,'.0','.0','.0','.0'),(19,6,21,6,22,'.0','.0','.0','.0'),(20,6,22,6,23,'.0','.0','.0','.0'),(21,6,23,6,24,'.0','.0','.0','.0'),(22,6,24,6,25,'.0','.0','.0','.0'),(23,6,25,6,26,'.0','.0','.0','.0'),(24,6,26,6,27,'.0','.0','.0','.0'),(25,6,27,6,28,'.0','.0','.0','.0'),(26,6,28,6,29,'.0','.0','.0','.0'),(27,6,29,6,30,'.0','.0','.0','.0'),(28,2,30,2,31,'177','8.0','.0','.0'),(29,2,31,2,32,'179','4.0','.0','.0'),(30,2,32,2,33,'179','8.0','.0','.0'),(31,2,33,2,34,'177','4.0','.0','.0'),(32,2,34,2,35,'177','.0','.0','.0'),(33,2,35,2,36,'24.0','.0','.0','.0'),(34,2,36,2,37,'177\0\0','.0','.0','.0'),(35,2,37,2,38,'64.5','.0','.0','.0'),(36,2,38,2,39,'114\0\0','.0','57.5','.0'),(37,4,39,4,40,'.0','.0','.0','.0'),(38,4,40,4,41,'.0','.0','.0','.0'),(39,4,41,4,42,'.0','.0','.0','.0'),(40,4,42,4,43,'.0','.0','.0','.0'),(41,4,43,4,44,'.0','.0','.0','.0'),(42,4,44,4,45,'.0','.0','.0','.0'),(43,4,45,4,46,'.0','.0','.0','.0'),(44,4,46,4,47,'.0','.0','.0','.0'),(45,4,47,4,48,'.0','.0','.0','.0'),(46,4,48,4,49,'.0','.0','.0','.0'),(47,4,49,4,50,'.0','.0','.0','.0'),(48,4,50,4,51,'.0','.0','.0','.0'),(49,4,51,4,52,'.0','.0','.0','.0'),(50,4,52,4,53,'.0','.0','.0','.0'),(51,4,53,4,54,'.0','.0','.0','.0'),(52,4,54,4,55,'.0','.0','.0','.0'),(53,4,55,4,56,'.0','.0','.0','.0'),(54,4,56,4,59,'.0','.0','.0','.0'),(55,4,57,4,60,'.0','.ERROR:`GenshenHR2`.`Times`:Inserting Data: Incorrect decimal value: '177' for column 'Hours' at row 34
For some reason it is interpreting the data in the 6th column 'Hours' with slashes (177\0\0) on rows 34 & 36 instead of the correct decimal format (177.5)
In MSSQL the column is: [Hours] NUMERIC(4,1) NULL DEFAULT 0
Its corresponding line in the Migration is: `Hours` DECIMAL(4,1) NULL DEFAULT 0
Ironically the following 3 columns (Holiday, Overtime & Unpaid) are all the same datatype, and looking at the log extract appear to insert without issue (see row 36 / 57.5).
Does anyone know why this might be happening?