mysql - using excel dates for importing into phpmyadmin sql

Question

I have a table in excel (CSV) which I am importing into phpmyadmin.

the cells show the date fine such as: 09/11/2012 0:00 The cell format is "custom"

when I import the table into phpmyadmin, all the dates turn to: 0000-00-00 00:00:00

What is the correct way to put them in excel so that I can import them into the database.

And how can I convert them to the correct format?

Answer 1

Format your excel dates into the format php accepts. E.g. yyyy-mm-dd h:mm:ss

In excel use the following steps:

• Right click the column heading that contains the dates.

  • Select Format Cells

  • Click the "Custom" category

  • Paste "yyyy-mm-dd h:mm:ss" in the input box.

*. Save the document.

In php SQL query, make sure to set this column to date in anycase if php side is going to treat the date as a string.

You may use : ‘STR_TO_DATE(@date, '%Y-%c-%e %H:%i:%S')‘