hi all,


right now i'm trying to migrate from db2 running under linux to mysql v5.1.

i manage to export out the db2 structure & data into a del (ascii) file.
but when i try to load the data from the del file to mysql table, it generate an error.

below is the load data infile syntax i use =
LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';


and below is the sample layout of del file (in the above case is TABLE01.del) =
1,0,"2007-07-31 00:25:12"
2,0,"2007-07-31 14:09:00"
3,0,"2007-07-31 00:00:00"
4,0,"2007-07-31 00:00:00"
5,0,"2007-07-31 00:00:00"


and below is the TABLE01 structure =
FieldName, Type, Null, Primary
------------------------------------------------------
MY_TABLEID, int(11), NO, PRIMARY KEY
CNTS, int(11), NO, NOT PRIMARY KEY
INSERT_DATE, datetime, NO, PRIMARY KEY


the error i encounter is =
ERROR 1292 (22007): Incorrect datetime value: '"2007-07-31 00:25:12".....


i do some trick by trying to remove the qoute sign from "2007-07-31 00:25:12" so the del file become =
1,0,2007-07-31 00:25:12
2,0,2007-07-31 14:09:00
3,0,2007-07-31 00:00:00
4,0,2007-07-31 00:00:00
5,0,2007-07-31 00:00:00

And i do load data infile again with same command =
LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
and it was successful.


to me it is very strange, since i can do a sql insert using "2007-07-31 00:25:12" for example: insert into TABLE01 values (11, 22, "2007-07-31 00:25:12") successfully without any error!!!
in fact, when i use a MySQL Administrator tools, and i export out the data from TABLE01 into CSV, then i found that the structure is exactly the same with del file generated by db2.

do i make any mistake??

or do i need to initialize something before i call
LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' ...?????


Any help is appreciated.
Thank you.


Regards,
Lanes

set default datetime format as yours. then try it again.