当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - using excel dates for importing into phpmyadmin sql

问题描述:

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?

网友答案:

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

网友答案:

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

分享给朋友:
您可能感兴趣的文章:
随机阅读: