In this tutorial we are writing MySQL query to sort upcoming birthdays based on current date, here writing three number of queries these may be help of to reach your result.
$sql = "SELECT * ,CASE WHEN BirthdayThisYear>=NOW() THEN BirthdayThisYear ELSE BirthdayThisYear + INTERVAL 1 YEAR END AS NextBirthday FROM ( SELECT * ,`emp_birthday` - INTERVAL YEAR(`emp_birthday`) YEAR + INTERVAL YEAR(NOW()) YEAR AS BirthdayThisYear FROM `hs_hr_employee` ) AS bdv WHERE `emp_birthday` != 'NULL' ORDER BY NextBirthday";
I tried to get the upcoming birthday from a employee table, for the next 90 days. Using :
$sql ="SELECT * FROM hs_hr_employee WHERE emp_status = '1' AND emp_birthday >= DATE_SUB(CURDATE(), INTERVAL YEAR(CURDATE()) - YEAR(emp_birthday) YEAR) AND emp_birthday <= DATE_ADD(DATE_SUB(CURDATE(), INTERVAL YEAR(CURDATE()) - YEAR(emp_birthday) YEAR), INTERVAL 365 DAY) ORDER BY emp_birthday";
$sql = "SELECT *, emp_birthday + INTERVAL(YEAR(CURRENT_TIMESTAMP) - YEAR(emp_birthday)) + 0 YEAR AS currbirthday, emp_birthday + INTERVAL(YEAR(CURRENT_TIMESTAMP) - YEAR(emp_birthday)) + 1 YEAR AS nextbirthday FROM hs_hr_employee WHERE emp_birthday !='NULL' AND emp_status='1' ORDER BY CASE WHEN currbirthday >= CURRENT_TIMESTAMP THEN currbirthday ELSE nextbirthday END";
This is Mukesh Jakhar. I’m a professional web developer with 3+ years experience. I am always ready for freelance work and i am writing blogs in my free time. I love to learn new technologies and share with others.
I founded PHPKIDA in September 2015. The focus of this website to provide web development tutorials of PHP, WordPress, CodeIgniter, Jquery, MySQL, HMTL, CSS etc and sharing solution to problems which i already solved.