使用excel日期导入phpmyadmin sql [英] using excel dates for importing into phpmyadmin sql
问题描述
单元格显示日期如下:09/11/2012 0:00
单元格格式为custom
当我将表格导入phpmyadmin时,所有日期都会变为:0000-00-00 00:00:00
什么是正确的方式把它们放在excel中,这样我就可以将它们导入到数据库中。
如何将它们转换成正确的格式?
将您的excel日期格式化为php接受的格式。例如。 yyyy-mm-dd h:mm:ss
在excel中使用以下步骤:
-
右键单击包含日期的列标题。
-
选择格式化单元格
-
点击自定义类别
-
粘贴yyyy-mm-dd h:mm:ss在输入框中。
-
*。保存文档。
在php SQL查询中,请确保将此列设置为anycase,如果php端将要将日期视为字符串。
您可以使用:'STR_TO_DATE(@date,'%Y-%c-%e%H:%i:%S')'
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')‘
这篇关于使用excel日期导入phpmyadmin sql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!