Date problem when importing from file into MySQL

I have a table with payDate DATETIME in which I'm inserting using load data local infile 'file.txt' into table tableName; dates like 26/04/2012 00:00:00.

This gives warnings like Warning | 1265 | Data truncated for column 'payDate' at row开发者_C百科 1 and the date in the table is 0000-00-00 00:00:00.

Is there any way to specify the format of the date?

Try STR_TO_DATE:

load data local infile 'file.txt' into table tableName 
SET payDate = str_to_date(@payDate, '%d/%m/%Y');

I worked out without converting from datetime to varchar , Below is the working code for it -

mysql query to export csv data to local directory

SELECT organization_id,bank_name,branch_name,account_number,statement_type,parameter_name,
parameter_value,created_by,creation_date,updated_by,updation_date FROM cm_sub_param_values
    INTO OUTFILE 'D:\mytable.csv'
    FIELDS ESCAPED BY '""'
    TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n';

In the csv exported , remove the column names and SAVE.

In the Table , creation_date and updation_date are of type datetime .

Import CSV data into mysql

    LOAD DATA INFILE 'D:/mytable.csv' IGNORE 
    INTO TABLE `cm_sub_param_values` 
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '\''
    LINES TERMINATED BY '\r\n' 
    (sub_param_id,organization_id,bank_name,branch_name,account_number,
statement_type,parameter_name,parameter_value,created_by,@creation_date,updated_by,@updation_date)
    SET creation_date = STR_TO_DATE(@creation_date, '%m/%d/%Y %H:%i'),
    updation_date = STR_TO_DATE(@updation_date, '%m/%d/%Y %H:%i') ;

Import Successful !

You would want to modify those variables before loading : Link

• set column type to VARCHAR
• LOAD DATA INFILE
• UPDATE SET column = DATE_FORMAT( str_to_date(column, '%d/%m/%Y'), '%Y-%m-%d' )
• set column type back to DATE
• SHOW WARNINGS