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

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

Answers


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.


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)

@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 

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.


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).


This is my query for the 30 days before check:

select id from users where 
((TO_DAYS(concat(DATE_FORMAT(NOW(),'%Y'), '-', DATE_FORMAT(date_of_birth, '%m-%d')))-TO_DAYS(NOW()))>=-30 
AND (TO_DAYS(concat(DATE_FORMAT(NOW(),'%Y'), '-', DATE_FORMAT(date_of_birth, '%m-%d')))-TO_DAYS(NOW()))<=0)
OR (TO_DAYS(concat(DATE_FORMAT(NOW(),'%Y'), '-', DATE_FORMAT(date_of_birth, '%m-%d')))-TO_DAYS(NOW()))>=(365-31)

and 30 days after:

select id from users where 
((TO_DAYS(NOW())-TO_DAYS(concat(DATE_FORMAT(NOW(),'%Y'), '-', DATE_FORMAT(date_of_birth, '%m-%d'))))>=-31 
AND (TO_DAYS(NOW())-TO_DAYS(concat(DATE_FORMAT(NOW(),'%Y'), '-', DATE_FORMAT(date_of_birth, '%m-%d'))))<=0)
OR (TO_DAYS(NOW())-TO_DAYS(concat(DATE_FORMAT(NOW(),'%Y'), '-', DATE_FORMAT(date_of_birth, '%m-%d'))))>=(365-30)

Need Your Help

How do I parse an XML file that's on a different web server?

javascript xml asp-classic xmldom

I have a list of training dates saved into an XML file, and I have a little javascript file that parses all of the training dates and spits them out into a neatly formatted page. This solution was...

How to show content of a nested table with a cursor in PL/SQL

oracle plsql cursor nested-table

I want to show the content of a nested table using a cursor. I've try the following but it does not work. The arguments in the "put_line" are not correct but I don't know why.