c# - 将日期时间格式从 csv 转换为 mysql 数据库

Question

我正在从 datetime 格式的 csv 中导出数据

20/02/2012 16:42:10,

和mysql表日期格式类似

2013-11-04 10:00:52

but after exporting it is displayed like this
0000-00-00 00:00:00

编辑后的完整代码

// MySqlConnection conn = new MySqlConnection("Data source = ; database = ;uid= ;pwd=faithful ;Convert Zero Datetime=true;");
        MySqlConnection conn = new MySqlConnection("Data source =; database =;uid=root ;pwd=H0tjava1;Convert Zero Datetime=true;");

        DataTable db = new DataTable();


        string strLoadData = "LOAD DATA LOCAL  INFILE 'I:/kiran/final test/finaltest12.csv' INTO TABLE tickets  FIELDS  terminated by ',' ENCLOSED BY '\"'  lines terminated by '\n' IGNORE 1 LINES (SiteId,@VarDateTime,Serial,DeviceId,AgentAID,VehicleRegistration,CarPark,SpaceNumber,GpsAddress,VehicleType,VehicleMake,VehicleModel,VehicleColour,IssueReasonCode,IssueReason,NoticeLocation,Points,Notes) SET DateTime  =  STR_TO_DATE(@VarDateTime,'%d/%m/%Y %H:%i:%s');";
        MySqlCommand cmd1 = new MySqlCommand(strLoadData, conn);


        cmd1.CommandTimeout = 6000;


        cmd1.Connection = conn;
        conn.Open();
        cmd1.Prepare();
        cmd1.ExecuteNonQuery();
        conn.Close();

错误

Fatal error encountered during command execution. at this line cmd1.ExecuteNonQuery();

Answer 1

像下面这样尝试

LOAD DATA LOCAL  INFILE 'F:/Explor/final test/finaltest12.csv' 
INTO TABLE tickets  FIELDS  terminated by ',' ENCLOSED BY '\"'  lines terminated by '\n' 
IGNORE 1 LINES 
(SiteId,@VarDateTime,Serial,DeviceId,AgentAID,VehicleRegistration,CarPark,SpaceNumber,GpsAddress,VehicleType,VehicleMake,VehicleModel,VehicleColour,IssueReasonCode,IssueReason,NoticeLocation,Points,Notes)
SET DateTime  =  STR_TO_DATE(@VarDateTime,'%d/%m/%Y %H:%i:%s');

此处 DateTime 是列的名称。