mysql中增加45天三小时_Mysql查询在接下来的45天内选择所有日期（仅限日期和月份）...

解决此问题的最佳方法是使用DAYOFYEAR . 当你在年底的45天内处理这种情况时，困难的部分是处理这种情况，在这种情况下，你必须拆分支票，看看日期是从现在到年底还是年初现在45天;否则你只需检查一年中的某一天是从现在到现在45天 . 尝试用以下方法替换您的情况：

CASE WHEN DAYOFYEAR('2018-12-31')-DAYOFYEAR(NOW()) < 45 THEN

DAYOFYEAR(p.service_date) BETWEEN DAYOFYEAR(NOW()) AND DAYOFYEAR('2018-12-31') OR

DAYOFYEAR(p.service_date) BETWEEN 1 AND DAYOFYEAR(NOW() + INTERVAL 45 DAY)

ELSE

DAYOFYEAR(p.service_date) BETWEEN DAYOFYEAR(NOW()) AND DAYOFYEAR(NOW() + INTERVAL 45 DAY)

END

您的完整查询应如下所示：

SELECT

p.*,

c.company

FROM

products p

LEFT JOIN

customers c

ON c.id = p.id

WHERE

(CASE WHEN DAYOFYEAR('2018-12-31')-DAYOFYEAR(NOW()) < 45 THEN

DAYOFYEAR(p.service_date) BETWEEN DAYOFYEAR(NOW()) AND DAYOFYEAR('2018-12-31') OR

DAYOFYEAR(p.service_date) BETWEEN 1 AND DAYOFYEAR(NOW() + INTERVAL 45 DAY)

ELSE

DAYOFYEAR(p.service_date) BETWEEN DAYOFYEAR(NOW()) AND DAYOFYEAR(NOW() + INTERVAL 45 DAY)

END)

AND c.email_service = 1

ORDER BY

p.service_date ASC

如果您担心闰年，可以将 DAYOFYEAR('2018-12-31') 更改为 DAYOFYEAR(CONCAT(YEAR(NOW), '-12-31'))