Mega Code Archive
Find out the age with procedure
<![CDATA[
DELIMITER $$
CREATE PROCEDURE AGE
(IN START_DATE DATE,
IN END_DATE DATE,
OUT YEARS INTEGER,
OUT MONTHS INTEGER,
OUT DAYS INTEGER)
BEGIN
DECLARE NEXT_DATE, PREVIOUS_DATE DATE;
SET YEARS = 0;
SET PREVIOUS_DATE = START_DATE;
SET NEXT_DATE = START_DATE + INTERVAL 1 YEAR;
WHILE NEXT_DATE < END_DATE DO
SET YEARS = YEARS + 1;
SET PREVIOUS_DATE = NEXT_DATE;
SET NEXT_DATE = NEXT_DATE + INTERVAL 1 YEAR;
END WHILE;
SET MONTHS = 0;
SET NEXT_DATE = PREVIOUS_DATE + INTERVAL 1 MONTH;
WHILE NEXT_DATE < END_DATE DO
SET MONTHS = MONTHS + 1;
SET PREVIOUS_DATE = NEXT_DATE;
SET NEXT_DATE = NEXT_DATE + INTERVAL 1 MONTH;
END WHILE;
SET DAYS = 0;
SET NEXT_DATE = PREVIOUS_DATE + INTERVAL 1 DAY;
WHILE NEXT_DATE <= END_DATE DO
SET DAYS = DAYS + 1;
SET PREVIOUS_DATE = NEXT_DATE;
SET NEXT_DATE = NEXT_DATE + INTERVAL 1 DAY;
END WHILE;
END$$
DELIMITER ;
SET @START = '1991-01-12'
;
SET @END = '1999-07-09'
;
CALL AGE (@START, @END, @YEAR, @MONTH, @DAY)
;
SELECT @START, @END, @YEAR, @MONTH, @DAY;
]]>