mySQL SELECT upcoming birthdays

Question

I'm trying to write a query to select users of a database whose birthdays are in the next 7 days.

I've done a lot of research but I can't come up with a working solution.

The birthday field is stored as a varchar eg '04/16/93' is there any way to work with this?

This is what I have so far:

SELECT * 
FROM   `PERSONS` 
WHERE  `BIRTHDAY` > DATEADD(DAY, -7, GETDATE()) 

I should have made it more clear, I'm trying to find birthdays not dates of birth. So i'm just looking for days and months not years.

Answer 1

To get all birthdays in next 7 days, add the year difference between the date of birth and today to the date of birth and then find if it falls within next seven days.

SELECT * 
FROM  persons 
WHERE  DATE_ADD(birthday, 
                INTERVAL YEAR(CURDATE())-YEAR(birthday)
                         + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birthday),1,0)
                YEAR)  
            BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);

If you want to exclude today's birthdays just change > to >=

SELECT * 
FROM  persons 
WHERE  DATE_ADD(birthday, 
                INTERVAL YEAR(CURDATE())-YEAR(birthday)
                         + IF(DAYOFYEAR(CURDATE()) >= DAYOFYEAR(birthday),1,0)
                YEAR)  
            BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);

-- Same as above query with another way to exclude today's birthdays 
SELECT * 
FROM  persons 
WHERE  DATE_ADD(birthday, 
                INTERVAL YEAR(CURDATE())-YEAR(birthday)
                         + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birthday),1,0)
                YEAR) 
            BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
     AND DATE_ADD(birthday, INTERVAL YEAR(CURDATE())-YEAR(birthday) YEAR) <> CURDATE();


-- Same as above query with another way to exclude today's birthdays 
SELECT * 
FROM  persons 
WHERE  DATE_ADD(birthday, 
                INTERVAL YEAR(CURDATE())-YEAR(birthday)
                         + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birthday),1,0)
                YEAR) 
            BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
     AND (MONTH(birthday) <> MONTH(CURDATE()) OR DAY(birthday) <> DAY(CURDATE()));

Here is a DEMO of all queries

Answer 2

Its very easy and simple. No need to use any if conditions or anything else you just need to use DATE_FORMAT() function of mysql.

Here is my sql query that is

SELECT id,email ,dob FROM `users` where DATE_FORMAT(dob, '%m-%d') >= DATE_FORMAT(NOW(), '%m-%d') and DATE_FORMAT(dob, '%m-%d') <= DATE_FORMAT((NOW() + INTERVAL +7 DAY), '%m-%d')

---

Answer 3

This is my solution. It also works if date of birth is January 1st and today's date is December 31.

SELECT `id`, `name`, `dateofbirth`,
    DATE_ADD(
        dateofbirth, 
        INTERVAL IF(DAYOFYEAR(dateofbirth) >= DAYOFYEAR(CURDATE()),
            YEAR(CURDATE())-YEAR(dateofbirth),
            YEAR(CURDATE())-YEAR(dateofbirth)+1
        ) YEAR
    ) AS `next_birthday`
FROM `user` 
WHERE 
    `dateofbirth` IS NOT NULL
HAVING 
    `next_birthday` BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
ORDER BY `next_birthday`
LIMIT 1000;

Answer 4

I did a lot "researches" and here is my solution, I guess it is very easy to understand!

SELECT *,
(366 + DAYOFYEAR(birth_date) - DAYOFYEAR(NOW())) % 366 as left_days
FROM `profile`
ORDER BY left_days;

Answer 5

Any solution using DAYOFYEAR() will be flawed when the date of birth occurred during a leap year. Consider using TIMESTAMPDIFF() when working with age or birthdays.

Current age can be calculated as

timestampdiff(YEAR, person.date_of_birth, curdate())

Calculate the upcoming birthday by adding the age to their date of birth.

DATE_ADD(
    person.date_of_birth, 
    INTERVAL timestampdiff(YEAR, person.date_of_birth, curdate())+1 YEAR
)

Putting this together to solve the OP’s query

select
    DATE_ADD(
        person.date_of_birth, 
        INTERVAL timestampdiff(YEAR, date_add(person.date_of_birth,INTERVAL 1 DAY), curdate())+1 YEAR
    ) upcoming_birthday
from person
having upcoming_birthday between curdate() and DATE_ADD(curdate(), INTERVAL 7 DAY)

Notice that I have added one day to date_of_birth when calculating age, otherwise today’s birthdays would not be returned.

Answer 6

I found this code to work really well:

DATE_ADD(user_birthdate, INTERVAL YEAR(FROM_DAYS(DATEDIFF(CURDATE(), user_birthdate)-1)) + 1 YEAR) AS next_birthday 

it actually really simple, it calculate the person age, then the current's year birthday and then add 1 year.

it based on the answer of Robert Eisele which you may find here: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

p.s.

with this solution you might fetch people who had a birthday yesterday (that's because the -1 in the FROM_DAYS calculation, but it is needed because of the leap years). this shouldn't consider you too much since you only want 7 days a head so you should just add the following condition:

HAVING next_birthday BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY) 

Answer 7

try this:

 Select * From persons
 where (DayOfYear(birthday) >= 7 
         And DayOfYear(birthday) - DayOfYear(curdate()) Between 0 and 6) Or
       (MOD(YEAR(curDate()),4) = 0) And MOD(YEAR(curDate()),100) != 0
        And (DayOfYear(birthday) + 366 - DayOfYear(curdate())) % 366 < 7) Or
         (DayOfYear(birthday) + 365 - DayOfYear(curdate())) % 365 < 7)

Answer 8

I managed to get it working with this query. Due mostly to the help of Lobo's answer.

SELECT * 
FROM  persons 
WHERE  DATE_ADD(STR_TO_DATE(birthday, '%m/%d/%Y'), INTERVAL YEAR(CURDATE())-YEAR(STR_TO_DATE(birthday, '%m/%d/%Y')) YEAR) 
            BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);

Answer 9

While trying to get the list who have their b'days upcoming in a range of time, we can run in to couple of issues.

When there is a leap year, then there is a chance that the condition you have fails to handle the case of leap years. Next problem could be like today is 2016-12-30 and you need b'days for next 7 days. So the end of the period is in year 2017. Some condition fail in this case. These are very important test cases.

Most of the fixed in this thread are using the DAYOFYEAR() which fails when you are on a leap year.

eg.

DAYOFYEAR('2016-03-01 00:00:00') is 61.
DAYOFYEAR('2015-03-01 00:00:00') is 60

Simplest and most easy to understand way is this.

1. Calculate the next upcoming b'day for a user.
2. Then check if the day comes in our range.

This works on leap years and also the range of dates span in two years.

SELECT  * 
FROM    `PERSONS` 
WHERE  
    /* 
       Here we calculate the next coming b'day for user 
       and check if it is between our span 
    */
    CONCAT(IF( 
            CONCAT( YEAR(CURDATE()), substring(`BIRTHDAY`, 5, length(`BIRTHDAY`))) < CURDATE(), 
            YEAR(CURDATE()) + 1, /* Adds an year if already past */
            YEAR(CURDATE())  /* Use this year if it is upcoming */
         ), substring(`BIRTHDAY`, 5, length(`BIRTHDAY`))) 
    BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL @tot DAY)

PS: Also it is the best solution if you want to order these on the basis of the b'days. You just need to add this as a field.

Answer 10

The accepted answer above from Lobo is flawed in that, if the query is run on December 31, and the user has a birthday on January 1, they will not be matched.

You must add logic to the query so that, if the birthday has already passed this year, you look at NEXT YEAR's birthday, not this year's. Details below:

SELECT *, IF( DAYOFYEAR( STR_TO_DATE(birthday, '%m/%d/%Y') ) < DAYOFYEAR( NOW() ),
DATE_ADD( STR_TO_DATE(birthday, '%m/%d/%Y'), INTERVAL YEAR( CURDATE() ) - YEAR( STR_TO_DATE(birthday, '%m/%d/%Y') ) + 1 YEAR ),
DATE_ADD( STR_TO_DATE(birthday, '%m/%d/%Y'), INTERVAL YEAR( CURDATE() ) - YEAR( STR_TO_DATE(birthday, '%m/%d/%Y') ) YEAR ) )
AS nextBirthday FROM persons
WHERE nextBirthday BETWEEN CURDATE() AND DATE_ADD( CURDATE(), INTERVAL 7 DAY);

Answer 11

We've had troubles with a calendar that had to show the next ten birthdays (including todays birthdays). I've reduced the solution I just found to the relevant parts:

SELECT first_name, last_name, birth_date,
    IF (DAYOFYEAR(DATE_FORMAT(birth_date,'1980-%m-%d %T')) < DAYOFYEAR(DATE_FORMAT(NOW(),'1980-%m-%d %T')) ,
          DAYOFYEAR(DATE_FORMAT(birth_date,'1980-%m-%d %T'))+368 ,
          DAYOFYEAR(DATE_FORMAT(birth_date,'1980-%m-%d %T'))) AS upcomming
FROM users
WHERE birth_date IS NOT NULL AND birth_date !=0
ORDER BY upcomming ASC 
LIMIT 0, 10

It sets every year (including the actual) to a leap year, so there won't be any problems with that. If you get close to the end of the year it's no problem, too. I'm quite statisfied to finally finding a working solution, so I wanted to share this, maybe it is of use for someone else :)

P.S.: If you don't want to show todays birthdays, just add a +1 after DAYOFYEAR(DATE_FORMAT(NOW(),'1980-%m-%d %T'))

Answer 12

This is what I did when I faced the same problem :

select * from users 
where ( month(date_of_birth) > month(CURDATE()) 
 and month(date_of_birth) < month(ADDDATE(CURDATE(),30)) ) 
 or ( month(CURDATE()) = month(date_of_birth) and day(date_of_birth) >= day(CURDATE()) 
 or month(ADDDATE(CURDATE(),30)) = month(date_of_birth) and day(date_of_birth) <= day(ADDDATE(CURDATE(),30)) ) 
 or ( year(CURDATE()) > year(ADDDATE(CURDATE(),30)) and month(date_of_birth) < month(CURDATE()) and month(date_of_birth) > month(ADDDATE(CURDATE(),30)) )

Answer 13

This is optimized solution for leap year problem

SELECT * 
FROM  persons 
WHERE DATE_FORMAT( CONCAT(YEAR(CURDATE()),'-',DATE_FORMAT(birth_date, '%m-%d') ), '%Y-%m-%d')
     BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)

Answer 14

SELECT * from persons as p
WHERE   
MOD( DAYOFYEAR(p.bday) - DAYOFYEAR(CURRENT_DATE()) + 366, 366)  
BETWEEN 0 and 7  
ORDER by DAYOFYEAR(p.bday) ASC

This works for me.

Answer 15

Building on Lobo's answer to tackle leap years

SELECT * FROM users
WHERE DATE_ADD(dob,INTERVAL YEAR(CURDATE())-YEAR(dob)
  + IF(MONTH(CURDATE()) > MONTH(dob), 1,
     IF(MONTH(CURDATE()) = MONTH(dob) AND DAY(CURDATE()) > DAY(dob), 1, 0))
       YEAR)
        BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)

Answer 16

So another approach you can take is to do a little more work before hitting the database layer. For example, I recently did this in a Laravel (PHP) and Postgres project. I basically built up an array of dates (ie. the next 7 days), and did a where in query to find users with birthdays on those dates.

User::whereNotNull('birth_date')
    ->where('birth_date', '<=', $endDate)
    ->whereIn(DB::raw("to_char(birth_date, 'MMDD')"), Carbon::range($startDate, $endDate)->map(function ($date) {
        return $date->format('md');
    }))->get();

Answer 17

Here is a simple PHP Code & SQL Query to retrieve upcoming birthdays. Where date of birth is stored as DATE (YYYY-MM-DD Format). enter image description here

<?php
$conn = mysqli_connect('localhost', 'user', 'paasword', 'databasename');
$nod = 5;     //Number of days upto which you want to retrieve birthdays
$delim = $filter = "";
for($i=0;$i<$nod;$i++){
    $date = date_create(date('Y-m-d'));
    date_add($date,date_interval_create_from_date_string("$i days"));
    $filter .= $delim."'".date_format($date,"m-d")."'";
    $delim = ", ";
}
$sql = "SELECT NAME, DOB, MOBILE, EMAIL, TITLE FROM tablename WHERE SUBSTR(DOB,6) IN ($filter) ORDER BY SUBSTR(DOB,6) ASC";
$result = mysqli_query($conn, $sql);    
$i=0;
echo '
<table class="table-1 mt-2 table-responsive table-bordered">
    <tr>
        <th>S. No.</th>
        <th>Name</th>
        <th>DOB</th>
        <th>MOBILE</th>
    </tr>';
while($row = mysqli_fetch_array($result)){
    $i++;
    $dob = date('Y-').date_format(date_create($row["DOB"]),'m-d');
    $day = date_format(date_create($dob),'w');
    switch ($day){
        case 0:
            $day = "Sunday"; break;
        case 1:
            $day = "Monday"; break;
        case 2:
            $day = "Tuesday"; break;
        case 3:
            $day = "Wednesday"; break;
        case 4:
            $day = "Thursday"; break;
        case 5:
            $day = "Friday"; break;
        case 6:
            $day = "Saturday"; break;
    }
    echo"
    <tr>
        <td>".$i."</td>
        <td>".$row["NAME"]."</td>
        <td>".date_format(date_create($row["DOB"]),'d-m-Y')." $day</td>
        <td>".$row["MOBILE"]."</td>
    </tr>
    ";
}
echo"
</table>";
?>

Answer 18

I think this solution may be more easy.

Create query string, something like, (I am using PHP and MySql)

$query = "SELECT cols FROM table WHERE MONTH(DobCol)= ".date('m')." AND (DAY(DobCol) >= ".date('d')." AND DAY(DobCol) <= ".(date('d')+$NumOfDaysToCheck).")";

Then execute this $query.