2

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?

Sackling
  • 1,780
  • 5
  • 37
  • 71

2 Answers2

5

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')‘

bonCodigo
  • 14,268
  • 1
  • 48
  • 91
  • 1
    Excel has a tendency to follow your system date format. So it is advisable that you keep your php SQL side ready in all cases to handle the excel feed date formats. [reference :](http://stackoverflow.com/questions/13420691/how-to-format-vba-textbox-to-long-date/13421587#13421587) – bonCodigo Nov 20 '12 at 21:04
0

I had this problem and thought I formatted them to format YYYY-MM-DD. They were displayed that way, but in the Formula bar it was still in DD/MM/YYYY format. So I actively changed the date with:

=YEAR(A1)&"-"&IF(MONTH(A1)<10;"0";"")&MONTH(A1)&"-"&IF(DAY(A1)<10;"0";"")&DAY(A1)

And then copied the values of these cells to replace the original ones...

Tushar Gupta - curioustushar
  • 58,085
  • 24
  • 103
  • 107
MatthiasVC
  • 3
  • 2