CODEWITHSUNDEEP
mysql
kamlesh rathore
kamlesh rathore

Reputation: 43

php mysql get upcoming birthdays in 7 days dd-MMM-yy

in my family data table i have dob column with format like dd-MMM-yy. so i need to get upcoming 7 days birthdays from this data. i ran query but not result showing... any solution ?

SELECT name, dob FROM persons  WHERE 
        DATE(CONCAT(YEAR(CURDATE()), LEFT(dob, 8)))
                BETWEEN 
                DATE_SUB(CURDATE(), INTERVAL 1 DAY)
                AND
                DATE_ADD(CURDATE(), INTERVAL 5 DAY)

The sample data are:

name        dob
prachi      09-Feb-15
badali devi 01-Jul-42
narayan ram 01-Jan-17
hasthi mal  01-Jan-18
bhiki devi  20-Jun-45

Upvotes: 0

Views: 354

Answers (1)

FanoFN
FanoFN

Reputation: 7124

This should work:

SELECT * FROM persons 
WHERE MONTH(STR_TO_DATE(dob, '%d-%b-%y'))=MONTH(CURDATE()) 
AND DAY(STR_TO_DATE(dob, '%d-%b-%y')) 
    BETWEEN DAY(curdate()) AND DAY(CURDATE()+INTERVAL 7 DAY) ;

Fiddle here : https://www.db-fiddle.com/f/rBaEMGygitjidzUBNMLJfz/5

Basically just turn the dob value into a standard MySQL date format using STR_TO_DATE and using somewhat similar to your BETWEEN operation.

More about STR_TO_DATE

EDIT:

It appears that I didn't handle the overlapping months properly. In that case, I think of another way is to artificially append the person's birth date with current year and then use it to check days interval. Like this:

SELECT * FROM persons WHERE 
REPLACE(STR_TO_DATE(dob, '%d-%b-%y'),YEAR(STR_TO_DATE(dob, '%d-%b-%y')),YEAR(CURDATE())) 
BETWEEN CURDATE() AND CURDATE()+INTERVAL 7 DAY;

Upvotes: 1

Related Questions