CSV date format to MySQL date format

Question

When I try to import my CSV file to MySQL, all the date become 0000-00-00.

CSV date format is DD/MM/YYYY like this 16/11/2016.

MySQL format is: YYYY/MM/DD. I am using phpmyadmin to import this. How to solve this date transfer?

Answer 1

The format DD/MM/YYYY is not a valid date in MySQL. You could use LOAD DATA along with STR_TO_DATE to parse the date strings into actual dates which MySQL can recognize:

LOAD DATA INFILE 'path/to/file.csv'
INTO TABLE yourTable FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' 
(
    col1, col2, @var1, col4
)
SET date_col = STR_TO_DATE(@var1, '%d/%m/%Y')

The above assumes that your table has 4 columns, and that the third column you are reading in from your CSV file is the troublesome date. The trick here is that each date string will get mapped on the fly into a valid date type, leaving you with the result you want.

Answer 2

I think, you need to change the date format before adding to MYSQL database.

$old_date = date('dd/mm/yyyy');            
$middle = strtotime($old_date);            
$new_date = date('yyyy/mm/dd', $middle);