mysql将日期转换为当前年份的相同日期 [英] mysql convert date to same date of current year

问题描述

如何用当前年份替换日期列中的年份?

How can I replace the year of a date column with that of the current year?

以下返回NULL

SELECT str_to_date(concat(year(now()), '-',
month(datecolumn), '-' ,day(datecolumn)), '%Y-%M-%D')
FROM TABLE

推荐答案

Khalid的答案在大多数情况下是正确的.年搞砸了！例如，如果您在datecol的值为'2016-02-29'且CURRENT_DATE为'2017-01-01'的情况下运行建议的查询，则会得到null.

Khalid's answer is correct most of the time. Leap year messes things up! If you run the proposed query where the value of datecol is '2016-02-29' and the CURRENT_DATE is '2017-01-01', for example, you get null.

执行此操作的另一种方法可以更优雅地处理leap年，如下所示:

An alternate way to do this that handles leap year more gracefully is like this:

SELECT DATE_FORMAT(
    MAKEDATE(YEAR(CURRENT_DATE()), DAYOFYEAR(datecol)),
    '%Y-%m-%d'
) `date`
FROM t

date的值应为2017-03-01.

The value of date here would be 2017-03-01.

编辑/说明:问题是，例如，将'2016-02-29'的年份更改为2017，会产生'2017-02-29'，这不是有效的日期.然后，运行DATE_FORMAT('2017-02-29'，'％Y-％m-％d')将导致null.问题的演示在这里:

Edit/clarification: The problem is that changing the year of '2016-02-29' to 2017, for example, produces '2017-02-29', which is not a valid date. Then, running DATE_FORMAT('2017-02-29', '%Y-%m-%d') results in null. A demo of the problem is here:

http://sqlfiddle.com/#!9/c5358/11

但是，在查看了我的答案之后，我意识到，自2月28日之后的a年中的任何日期，对于具有365天的正常"年份，其天数是+1，这是因为我使用了MAKEDATE.例如，如果datecol ='2016-03-01'且当前年份为2017，则转换的日期将为'2017-03-02'，而不是所需的'2017-03-01'.更好的方法如下:

However, after reviewing my answer I realized that I another problem by using MAKEDATE since any date on a leap year after Feb 28 is days+1 for a "normal" year with 365 days. For example, if datecol = '2016-03-01' and the current year were 2017 then the converted date would be '2017-03-02', not '2017-03-01' as desired. A better approach is as follows:

SELECT
DATE_FORMAT(DATE_ADD(datecol, INTERVAL (YEAR(CURRENT_DATE()) - YEAR(datecol)) YEAR), '%Y-%m-%d') `date`
FROM t;

此方法将2月29日转换为28，否则将所有其他日期保留为您期望的日期.解决方案的演示在这里:

This method turns any Feb 29th into the 28th, and otherwise keeps all other dates exactly as you'd expect them. A demo of the solution is here:

http://sqlfiddle.com/#!9/c5358/12

这篇关于mysql将日期转换为当前年份的相同日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！