MySQL 更改mysql日期中的年份
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14491906/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Changing year in mysql date
提问by Andelas
I have a bunch of dates in our database stored in the standard mysql date type.
我的数据库中有一堆日期存储在标准的 mysql 日期类型中。
How can I covert a year to 2013, regardless of original date.
无论原始日期如何，我如何将一年转为 2013 年。
So if a date is 2009-01-01 it would be 2013-01-01, but if it's 2012-01-04, it'd convert to 2013-01-14.
因此，如果日期是 2009-01-01，它将是 2013-01-01，但如果是 2012-01-04，它将转换为 2013-01-14。
I figured it'd be simple and obvious, but I couldn't figure it out =/
我认为这很简单明了，但我想不通=/
回答by Adi
That's simple:
这很简单：
for DATETIME:
日期时间：
UPDATE table_name
SET date_col=DATE_FORMAT(date_col,'2013-%m-%d %T');
for DATE:
日期：
UPDATE table_name
SET date_col=DATE_FORMAT(date_col,'2013-%m-%d');
回答by recneps
The problem with the current answers is that none of them take leap year into account. If you take the date '2016-02-29' and convert it to the year 2013 through concatenation, you get '2013-02-29', which is not a valid date. If you run DATE_FORMAT('2013-02-29', '%Y-%m-%d') the result is null. See an example here:
当前答案的问题在于它们都没有考虑闰年。如果取日期“2016-02-29”并通过串联将其转换为 2013 年，则会得到“2013-02-29”，这不是有效日期。如果您运行 DATE_FORMAT('2013-02-29', '%Y-%m-%d') 结果是null. 在此处查看示例：
http://sqlfiddle.com/#!9/c5358/11
http://sqlfiddle.com/#!9/c5358/11
A better way to change the year is to use DATE_ADD since it accounts for daylight savings. For example:
更改年份的更好方法是使用 DATE_ADD，因为它考虑了夏令时。例如：
SELECT
DATE_FORMAT(DATE_ADD(datecol, INTERVAL (YEAR(CURRENT_DATE()) - YEAR(datecol)) YEAR), '%Y-%m-%d') `date`
FROM t;
You could substitute CURRENT_DATE() with '2013-01-01' if you still wanted to convert all dates to 2013 instead of the current year. An example of this solution is here:
如果您仍想将所有日期转换为 2013 年而不是当前年份，则可以将 CURRENT_DATE() 替换为“2013-01-01”。此解决方案的示例如下：
回答by John Woo
UPDATE tableName
SET dateColumn = dateColumn + INTERVAL 4 YEAR
other way is to concatenate it,
另一种方法是连接它，
UPDATE Table1
SET DateColumn = CONCAT(YEAR(CURDATE()), '-', DATE_FORMAT(dateColumn, '%m-%d'))
回答by nathan hayfield
If its a date field:
如果它是一个日期字段：
UPDATE table_name SET date_field_name = CONCAT("2013", RIGHT(date_field_name,6));
If its a date time field:
如果它是一个日期时间字段：
UPDATE table_name SET date_field_name = CONCAT("2013", RIGHT(date_field_name,15));
回答by Rogerio de Moraes
Current date from quest was 2013, I understand that you wish set current YEAR in date.
任务的当前日期是 2013 年，我知道您希望将当前年份设置为日期。
UPDATE table_name SET date_col=DATE_FORMAT('2013-05-06',YEAR(CURRENT_DATE)-%m-%d);
