CODEWITHSUNDEEP
mysqlsqldatabase
Danny
Danny

Reputation: 343

mySQL SELECT upcoming birthdays

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.

Upvotes: 18

Views: 41912

Answers (18)

Amit Arora
Amit Arora

Reputation: 1

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.

Upvotes: 0

Ian
Ian

Reputation: 61

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.

Upvotes: 6

Nihal Tabhane
Nihal Tabhane

Reputation: 21

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)

Upvotes: 2

Rasulbek Abdurasulov
Rasulbek Abdurasulov

Reputation: 111

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;

Upvotes: 5

Rakesh
Rakesh

Reputation: 505

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

Upvotes: 13

Ankit Verma
Ankit Verma

Reputation: 1

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>";
?>

Upvotes: 0

Jonathan
Jonathan

Reputation: 19099

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();

Upvotes: 0

Tinnin
Tinnin

Reputation: 5

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)

Upvotes: 0

rrk
rrk

Reputation: 15846

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.

Upvotes: 2

VIVEK SEDANI
VIVEK SEDANI

Reputation: 407

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

Upvotes: 1

Papick G. Taboada
Papick G. Taboada

Reputation: 575

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.

Upvotes: 0

Praveen Lobo
Praveen Lobo

Reputation: 7187

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

Upvotes: 57

Todor
Todor

Reputation: 16010

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;

Upvotes: 6

Fanmade
Fanmade

Reputation: 306

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

Upvotes: 1

Tom McQuarrie
Tom McQuarrie

Reputation: 1147

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

Upvotes: 2

Roey
Roey

Reputation: 1725

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)

Upvotes: 3

Danny
Danny

Reputation: 343

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

Upvotes: 2

Charles Bretana
Charles Bretana

Reputation: 146409

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)

Upvotes: 2

Related Questions