MySQL-DATE_ADD月间隔 [英] MySQL - DATE_ADD month interval

问题描述

我在MySQL中遇到函数DATE_ADD的问题.

I face a problem with the function DATE_ADD in MySQL.

我的请求如下:

SELECT * 
FROM mydb 
WHERE creationdate BETWEEN "2011-01-01" AND DATE_ADD("2011-01-01", INTERVAL 6 MONTH) 
GROUP BY MONTH(creationdate)

问题在于，结果-我认为-因为 June 只有30天，所以该功能无法正常工作，因为我有 July的第一个结果.

The problem is that, in the results, -I think- because June has only 30 days, the function doesn't work properly as I have the results of the first of July.

是否有办法告诉DATE_ADD正常工作并在一个月内度过正确的天数?

Is there a way to tell DATE_ADD to work well and take the right number of days within a month?

推荐答案

DATE_ADD在不同月份都可以正常工作.问题是您要在2001-01-01中添加六个月，并且应该在7月1日.

DATE_ADD works just fine with different months. The problem is that you are adding six months to 2001-01-01 and July 1st is supposed to be there.

这是您想要做的:

SELECT * 
FROM mydb 
WHERE creationdate BETWEEN "2011-01-01" 
                   AND DATE_ADD("2011-01-01", INTERVAL 6 MONTH) - INTERVAL 1 DAY
GROUP BY MONTH(creationdate)

OR

SELECT * 
FROM mydb 
WHERE creationdate >= "2011-01-01" 
AND creationdate < DATE_ADD("2011-01-01", INTERVAL 6 MONTH)
GROUP BY MONTH(creationdate)

要进一步学习，请查看 DATE_ADD文档.

For further learning, take a look at DATE_ADD documentation.

*已编辑以更正语法

这篇关于MySQL-DATE_ADD月间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！