2

I need to write a FLOOR clause in my code to convert this piece of code from a function in which uses a bdate column to workout the age of every employee to finding the median age in the table.

DELIMITER \\
DROP FUNCTION IF EXISTS getAge;
CREATE FUNCTION getAge(pdate DATE) RETURNS int(11)
BEGIN
DECLARE years INT;
DECLARE months INT;
DECLARE days INT;
DECLARE age INT;

SELECT YEAR(pdate) INTO years;
SELECT MONTH(pdate) INTO months;
SELECT DAY(pdate) INTO days;

IF ( MONTH(CURRENT_DATE()) > months ) THEN
SELECT ( YEAR(CURRENT_DATE()) - YEAR(pdate) ) INTO age;
ELSEIF ( MONTH(CURRENT_DATE()) < months ) THEN
SELECT ( ( YEAR(CURRENT_DATE()) - YEAR(pdate) ) -1 ) INTO age;
ELSE
IF ( DAY(CURRENT_DATE()) >= DAY(pdate) ) THEN
SELECT ( YEAR(CURRENT_DATE()) - YEAR(pdate) ) INTO age;
ELSE
SELECT ( ( YEAR(CURRENT_DATE()) - YEAR(pdate) ) -1 ) INTO age;
END IF;
END IF;

RETURN age;
END \\
DELIMITER ;

SELECT getAge(bdate)
FROM employee
Mike Lischke
  • 36,881
  • 12
  • 88
  • 141
Zachary Betterridge
  • 33
  • 5
  • Possible duplicate of [How to get the difference in years from two different dates?](https://stackoverflow.com/questions/7749639/how-to-get-the-difference-in-years-from-two-different-dates) – Alexander Aug 13 '17 at 07:24
  • I need the median age in the already worked out ages for employees. so the median age in like 40 ages. – Zachary Betterridge Aug 13 '17 at 07:33
  • 1
    So then have a look at [Simple way to calculate median with MySQL](https://stackoverflow.com/questions/1291152/simple-way-to-calculate-median-with-mysql). Fun fact: you can calculate the median birthdate (which will be faster than calculating the median from the age as a result of your function; and it could also be precalculated until you get new employees or fire someone), and then calculate the current age for that birthday. – Solarflare Aug 13 '17 at 08:15

0 Answers0