Search code examples
mysqlsqlload-data-infile

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 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?

Solution

  • Try STR_TO_DATE:

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