SQL that list all birthdays within the next and previous 14 days

Question

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)

I'm trying to find the correct SQL to:

• List all birthdays within the next 14 days

and another query to:

• List all birthdays within the previous 14 days

Directly comparing the current date by:

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

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

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! :)

Answer 1

There are a number of options, I would first try to transform by number of years between current year and row's year (i.e. Add their age).

Another option is day number within the year (but then you have still to worry about the rollover arithmetic or modulo).

Answer 2

Easy,

We can obtain the nearer birthday (ie the birthday of this year) by this code:

dateadd(year,datediff(year,dob,getdate()),DOB)

use this in your compares ! it will work.

Answer 3

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

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.