是否可以在MySQL列中更改日期值的格式? [英] Is it possible to change format of date values in a MySQL column?
问题描述
今天使用的数据库是5年前建立的,某些日期字段/列中的条目采用欧洲字符串格式,显示29.05.2013而不是2013-05-29,后者是标准的最后一个。
今天遗憾地进行了设置,但是由于对这一主题的知识不足,这种方式在几年前就已经完成了。
p>是否可以重写这些列值?或作为第二种解决方案,也许是在过渡期间从旧列中获取数据并将其写入新列中?
每行都有唯一的ID - testid
数据库如下所示:
testid date1 date2 date3
--------------------------------- -----------------
001 2012-01-01 02.01.2012 04.01.2012
002 2012-03-03 05.03.2012 10.03.2012
在 date1
中,我们使用时间戳,因此它工作正常。真的希望其他列具有相同的格式。
希望有人比我有更好的专业知识。
更新t set date2 = str_to_date(date2,'%d。%m。%Y'),date3 = str_to_date(date3,'%d。%m。%Y' );
alter table t修改date2日期;
alter table t修改date3日期;
The database in use today was made 5 years ago and the entries in some of the date fields/columns are in a european string format, diplaying 29.05.2013 instead of 2013-05-29, the last one somewhat of a standard.
Regret the setup today, but it was done this way years ago due to lack of knowledge on the subject.
Is it possible to rewrite these column values? Or as a second solution, perhaps taking data from the old column and writing them to a new column while doing the transition?
Every row has a unique ID - testid
Database looks like this:
testid date1 date2 date3
--------------------------------------------------
001 2012-01-01 02.01.2012 04.01.2012
002 2012-03-03 05.03.2012 10.03.2012
In date1
we're using timestamp, so it works fine. Would really like to have the other columns in the same format.
Hoping that someone with better know-how than myself have some suggestions.
update t set date2=str_to_date(date2, '%d.%m.%Y'), date3=str_to_date(date3,'%d.%m.%Y');
alter table t modify date2 date;
alter table t modify date3 date;
这篇关于是否可以在MySQL列中更改日期值的格式?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!