Load Infile - Not able to import microsecond level datetime from CSV

[Chet Desai]

I am not able to load data from CSV file that has a datetime field that has microsecond level granularity.

mysql> desc gtp;
+--------------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------+------+-----+---------+-------+
| Papu_Id | tinyint(4) | YES | | NULL | |
| Physical_Papu_Id | tinyint(4) | YES | | NULL | |
| Report_Time | datetime | YES | | NULL | |
| Ul_Bytes | int(11) | YES | | NULL | |
| Dl_Bytes | int(11) | YES | | NULL | |
| Ave_Buf_Fill_Denom | tinyint(4) | YES | | NULL | |
| Ave_Buf_Fill_Numer | tinyint(4) | YES | | NULL | |
+--------------------+------------+------+-----+---------+-------+


mysql> load data infile 'C:\\temp\\Test_GTP.dat' into
-> table gtp
-> fields terminated by ','
-> lines terminated by '\r\n'
-> (Papu_Id, Physical_Papu_Id, @Rpt, Ul_Bytes, Dl_Bytes, Ave_Buf_fill_Denom,
Ave_Buf_Fill_Numer)
-> set Report_Time = str_to_date(@Rpt, '%Y-%m-%d %H:%i:%s.%f');

ERROR 1292 (22007): Truncated incorrect datetime value: '2010-07-18 00:00:07.020
000000'



Test_GTP.dat :
==============
0,0,2010-07-18 00:00:07.020000000,10543890,110391275,6,0
1,1,2010-07-18 00:00:07.020000000,3652696,47139832,6,0




But following works:
====================
mysql> select str_to_date('2010-07-18 00:00:07.020000000', '%Y-%m-%d %H:%i:%s.%f
');
+----------------------------------------------------------------------+
| str_to_date('2010-07-18 00:00:07.020000000', '%Y-%m-%d %H:%i:%s.%f') |
+----------------------------------------------------------------------+
| 2010-07-18 00:00:07.020000 |
+----------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)




Thank you in advance !!

-Chetan.