列出未来14天内所有生日的SQL [英] SQL that list all birthdays within the next and previous 14 days

问题描述

我有一个MySQL member表，带有一个DOB字段，该字段以DATE格式存储所有成员的生日(注意:它具有年份"部分)

I have a MySQL member table, with a DOB field which stores all members' dates of birth in DATE format (Notice: it has the "Year" part)

我试图找到正确的SQL来

I'm trying to find the correct SQL to:

• 列出未来14天内的所有生日

和另一个查询:

• 列出过去14天内的所有生日

通过以下方式直接比较当前日期:

Directly comparing the current date by:

(DATEDIFF(DOB, now()) <= 14 and DATEDIFF(DOB, now()) >= 0)

由于当前年份和DOB年不同，因此不会获取任何内容.

will fetch nothing since the current year and the DOB year is different.

但是，将DOB转换为今年"根本不起作用，因为今天可能是1月1日，而候选人的DOB可能是12月31日(反之亦然)

However, transforming the DOB to 'this year' won't work at all, because today could be Jan 1 and the candidate could have a DOB of Dec 31 (or vice versa)

如果您能伸出援助之手，那就太好了，非常感谢！ :)

It will be great if you can give a hand to help, many thanks! :)

推荐答案

我的第一个想法是，仅使用DAYOFYEAR并取得不同就容易了，但是实际上在开始/结束时有点花招.但是:

My first thought was it would be easy to just to use DAYOFYEAR and take the difference, but that actually gets kinda trick near the start/end of a yeay. However:

WHERE 
DAYOFYEAR(NOW()) - DAYOFYEAR(dob) BETWEEN 0 AND 14 
OR DAYOFYEAR(dob) - DAYOFYEAR(NOW())  > 351

应该有效，具体取决于您对leap年的关注程度.一个更好"的答案可能是从dob中提取DAY()和MONTH()并使用MAKEDATE()建立当前(或潜在的过去/之后)年份的日期并与之进行比较.

Should work, depending on how much you care about leap years. A "better" answer would probably be to extract the DAY() and MONTH() from the dob and use MAKEDATE() to build a date in the current (or potential past/following) year and compare to that.

这篇关于列出未来14天内所有生日的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！