MySQL Forums
Forum List  »  Stored Procedures

Human age function tip
Posted by: Aleksander Kujbida
Date: January 26, 2009 04:06AM

I couldn't find this anywhere, seems like something folks might have use for. Age in human-style: we speak of newborns in days, infants in weeks then later months, then finally we speak in terms of years. This function does just that, easily modified to suit preferences in your area or even to be made flexible about the hardcoded boundaries. Returns the age suffixed with units.

bd short for is birthdate

set global log_bin_trust_function_creators=1;
DROP FUNCTION IF EXISTS age_human;
DELIMITER //
CREATE FUNCTION age_human (bd datetime) returns char(10)
BEGIN
SET @age = DATEDIFF(CURDATE(), bd); #age in days
IF @age < 14 THEN RETURN CONCAT(@age, 'd'); #return age in days if less than 14d
ELSEIF @age < 56 THEN RETURN CONCAT(FLOOR(@age/7),'w'); #return age in weeks if less than 8 weeks
ELSE
SET @age = PERIOD_DIFF(CONCAT( YEAR(CURDATE()), SUBSTRING(CURDATE(),6,2) ), CONCAT(YEAR(bd), SUBSTRING(bd,6,2) ) ); #age in months
END IF;
IF @age < 37 THEN return CONCAT(@age, 'm'); #return age in months if less than 37months
ELSE return CONCAT( ((YEAR(CURDATE())-YEAR(bd)) - (RIGHT(CURDATE(),5)<RIGHT(bd,5))), 'y'); #otherwise return age in years
END IF;
END//
DELIMITER ;
DROP TABLE IF EXISTS person;
CREATE TABLE `person` (
`name` char(20) default NULL,
`dob` date default NULL
);
INSERT INTO person VALUES ('baby1', SUBDATE(CURDATE(), 9) ), ('baby2', SUBDATE(CURDATE(),75) ), ('toddler1', SUBDATE(CURDATE(), 400) ), ('other1', SUBDATE(CURDATE(), 4000) );
SELECT name, dob, curdate(), age_human(dob) FROM person;

Options: ReplyQuote


Subject
Views
Written By
Posted
Human age function tip
3744
January 26, 2009 04:06AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.