MySQl DATE格式将Varchar更新为不同格式的日期 [英] MySQl DATE Format updating Varchar to date in different format

问题描述

我有一些办公室记录。数据库已创建并填充了大量数据。我必须在其上创建搜索功能。但是问题是数据库有日期字段，它是var char形式，并且是DD-MM-YYYY形式例如：18-11-2011
我想转换日期格式的日期列丢失此数据。我试着在测试表上做，一切都变为零。

解决方案

div>

要扩展flesk的回答添加一个新列

  ALTER TABLE foo ADD newdate DATE; 
  

更新表格以填充此新列（例如flesk）

  UPDATE foo SET newdate = str_to_date（olddate，'％d-％m-％Y'）; 
  

然后您甚至可以测试转换是否正确。

  SELECT * FROM foo WHERE olddate<> DATE_FORMAT（newdate，'％d-％m-％Y'）; 
  

然后，您可以删除旧列，并重命名新列。或者只是保留原样。

I have some office records. Database is already created and filled with lots of data. I have to create search functionality on it. But the problem is database have date field which is in var char form and is in the form "DD-MM-YYYY" eg :"18-11-2011" I want to convert the date column in date format without losing this data. I tried doing it on test table and everything turned to zero. eg: 0000-00-00

What can be done for the same ?

解决方案

To expand on flesk's answer add a new column

ALTER TABLE foo ADD newdate DATE;

Update the table to fill this new column (like flesk did)

UPDATE foo SET newdate=str_to_date(olddate, '%d-%m-%Y');

Then you can even test if the conversion is correct.

SELECT * FROM foo WHERE olddate  <> DATE_FORMAT(newdate, '%d-%m-%Y');

Then you can either drop old column and rename new one. Or just leave it as it is.

这篇关于MySQl DATE格式将Varchar更新为不同格式的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！