列出未来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屋!