Michael Michael - 1 year ago 50
MySQL Question

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?

Eli Eli
Answer Source

Try STR_TO_DATE:

load data local infile 'file.txt' into table tableName 
SET payDate = str_to_date(@payDate, '%d/%m/%Y');
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download