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

@Eli had a good response, but hardcoding 351 makes it a little confusing and gets off by 1 during leap years.

This checks if birthday (dob) is within next 14 days. First check is if in same year. Second check is if its say Dec 27, you'll want to include Jan dates too.

With DAYOFYEAR( CONCAT(YEAR(NOW()),'-12-31') ), we are deciding whether to use 365 or 366 based on the current year (for leap year).

SELECT dob
FROM birthdays
WHERE DAYOFYEAR(dob) - DAYOFYEAR(NOW()) BETWEEN 0 AND 14 
OR 
DAYOFYEAR( CONCAT(YEAR(NOW()),'-12-31') ) - ( DAYOFYEAR(NOW()) - DAYOFYEAR(dob) ) BETWEEN 0 AND 14 

Answer 2

Here's the simplest code to get the upcoming birthdays for the next x days and previous x days

this query is also not affected by leap-years

SELECT name, date_of_birty 
FROM users 
WHERE DATE(CONCAT(YEAR(CURDATE()), RIGHT(date_of_birty, 6)))
          BETWEEN 
              DATE_SUB(CURDATE(), INTERVAL 14 DAY)
          AND
              DATE_ADD(CURDATE(), INTERVAL 14 DAY)