SQL listing all birthdays during the next and previous 14 days

Question

I have a MySQL member table with a DOB field that stores the birth dates of all participants in DATE format (Note: it has a "Year" part)

I am trying to find the correct SQL for:

• A list of all birthdays over the next 14 days.

and another request:

• A list of all birthdays in the previous 14 days.

Direct comparison of the current date with:

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

nothing will be received from the current year, and the DOB year is different.

However, converting the DOB to "this year" will not work at all, because today it may be January 1, and the candidate may have a DO of December 31 (or vice versa)

It would be great if you could help, thanks a lot! :)

Answer 1

My first thought was that it would just be easy to use DAYOFYEAR and take into account the difference, but actually it turns out a curious trick near the beginning / end of yay. But:

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

It should work, depending on how much you care about leap years. The “best” answer would probably be to extract DAY () and MONTH () from the dob and use MAKEDATE () to create a date in the current (or future past / next) year and match it.

Answer 2

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

leap years are not affected by this request either

 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) 

Answer 3

@Eli got a good answer, but hardcoding 351 makes it a bit confusing and goes out at 1 for leap years.

This checks to see if there will be a birthday (dob) over the next 14 days. The first check - if in the same year. The second check - if you say it on December 27, you also want to include the January dates.

With DAYOFYEAR( CONCAT(YEAR(NOW()),'-12-31') ) we decide whether to use 365 or 366 based on the current year (for a 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 4

Just,

We can get a closer birthday (i.e. the birthday of this year) by this code:

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

use it in your comparisons! he will work.

Answer 5

There are several options, I would first try to convert the number of years between the current year and the year in a row (i.e. add your age).

Another option is the day number for the year (but then you still need to worry about poll arithmetic or module).

Answer 6

This is my request 30 days before the 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)