http://dev.mysql.com/doc/refman/5.1/en/date-and-time-Functions.html#Function_date-format”rel=“noretinger” STR_TO_DATE(str, Format) MySQL履行职能。

转换成一个:

BEGIN;
 ALTER TABLE `date_test` 
  ADD COLUMN `my_date_col_converted` DATE;

 UPDATE `date_test` 
  SET `my_date_col_converted` = STR_TO_DATE(`my_date_col`,  %d/%c/%Y );

 ALTER TABLE `date_test` 
  DROP COLUMN `my_date_col`;

 ALTER TABLE `date_test` 
  CHANGE COLUMN `my_date_col_converted` `my_date_col` DATE;
COMMIT;

您可使用 DATE:

STR_TO_DATE( datefield , "%d/%m/%Y" )

If you need this DATE in a specific format, you can use DATE_FORMAT().
This probably isn t necessary in your case, but here s an example for completeness:

DATE_FORMAT( STR_TO_DATE( datefield , "%d/%m/%Y" ) , "%Y/%m/%d" )

因此,你可以在整个表格上用单一<代码>UPDATE将现有数据改为重新编号的数据(同时保持数据类型不变):

UPDATE tableName
SET originalDate = DATE_FORMAT(STR_TO_DATE(originalDate,"%d/%m/%Y" ),"%Y/%m/%d" );

Or, if you want to convert the datatype of the column DATE you could alter the table to create a new DATE formatted column, use the above update to fill that column, remove the original column, and then (optionally) rename the new column to the old name.

ALTER tableName
ADD modifiedDate DATE;

UPDATE tableName
SET modifiedDate = DATE_FORMAT( STR_TO_DATE( originalDate ,"%d/%m/%Y" ) ,"%Y/%m/%d" );

ALTER tableName
DROP COLUMN originalDate; 

ALTER tableName
CHANGE COLUMN modifiedDate originalDate;

这应当行之有效,但它并没有:

BEGIN;
 ALTER TABLE `date_test` 
  ADD COLUMN `my_date_col_converted` DATE;
 UPDATE `date_test` 
  SET `my_date_col_converted` = STR_TO_DATE(`my_date_col`,  %d/%c/%Y );
 ALTER TABLE `date_test` 
  DROP COLUMN `my_date_col`;
 ALTER TABLE `date_test` 
  CHANGE COLUMN `my_date_col_converted` `my_date_col` DATE;
COMMIT;

还应当努力: 工作

UPDATE db_test SET anticipated_court_date = DATE_FORMAT(STR_TO_DATE(anticipated_court_date,"%d/%m/%Y" ),"%Y-%m-%d" );

服务器版本:5.0.95-社区-log MySQL 社区版(GPL)

然而,这项工作:

SELECT STR_TO_DATE( 8/31/12 ,  %m/%d/%Y ); // WORKS

利用MySQL——我找不到任何可靠的解决办法。 即便是同一确切日期,也成功地转换了。

The solution I ve found is this:  PHP
$user_date = "8/31/12"; // WORKS
$mysql_date = date( Y-m-d H:i:s , strtotime($user_date));

以上是有助益的,但以下工作对我来说是关于我的。

ALTER TABLE `tablename` ADD `newcolumn` DATE NOT NULL ;
UPDATE `tablename` SET `newcolumn`=STR_TO_DATE(`oldcolumn`,  %d/%c/%Y )

现在删除旧栏(如果你愿意的话)。