Bug #23340 Calculate Age Function (days,months and years of age)

• Submitted: 16 Oct 2006 17:01
• Reporter: Luis Antonio Castro Henriques
• Status: Open
• Category: MySQL Server
• Severity: S4 (Feature request)
• CPU Architecture: Any
• Tags: age, FUNCTION

[16 Oct 2006 17:01] Luis Antonio Castro Henriques

Description:
Implementation of a group of functions to facilitate the age calculation.

The algoritm I´m using (see below) is already overly complex and also gives wrong results in some cases because of the lap years. To fix that I´ll need even more CASEs and IFs.

You could argue that will be better to implement the algoritm directly in the application, but for performance reasons, I don´t have this option.

How to repeat:
I´m usign this construct to calculate the numbers of days, months and years of an age:

DATE_FORMAT(now(),'%Y')-DATE_FORMAT(birth_date,'%Y') - (DATE_FORMAT(NOW(),'00-%m-%d')<DATE_FORMAT(birth_date,'00-%m-%d')) as age_years,

CASE SIGN(DATE_FORMAT(NOW(),'%m')-DATE_FORMAT(birth_date,'%m')) 
WHEN 0 THEN 0 
WHEN -1 WHEN (12 - DATE_FORMAT(birth_date,'%m') + DATE_FORMAT(NOW(),'%m') - (DATE_FORMAT(NOW(),'00-00-%d') < DATE_FORMAT(birth_date,'00-00-%d')))
WHEN 1 THEN(DATE_FORMAT(NOW(),'%m') - DATE_FORMAT(birth_date,'%m') - (DATE_FORMAT(NOW(),'00-00-%d') < DATE_FORMAT(birth_date,'00-00-%d'))) END as age_months,

CASE SIGN(DATE_FORMAT(NOW(),'%d') - DATE_FORMAT(birth_date,'%d')) 
WHEN 0 THEN 0 
WHEN -1 THEN (DATE_FORMAT(LAST_DAY(birth_date),'%d') + DATE_FORMAT(NOW(), '%d')  - DATE_FORMAT(birth_date, '%d'))
WHEN 1 THEN (DATE_FORMAT(NOW(),'%d') - DATE_FORMAT(birth_date,'%d') - (DATE_FORMAT(NOW(),'%d') < DATE_FORMAT(birth_date,'%d')))
END as age_days

But this will not give me the correct numbers in the case of a lap year. 
Example => birth_date ='1996-02-29' and now() = '2006-02-28'

Suggested fix:

[20 Feb 2014 8:19] Gauravkumar Mishra

Calculates Age in Years Months and Days, even considering the Leap Years.

Attachment: Age_Calc_Years_Months_Days.txt (text/plain), 11.08 KiB.

[20 Feb 2014 8:22] Gauravkumar Mishra

Calculates Age in Years Months and Days, even considering the Leap Years too

Attachment: mysql-bug-Age_Calc_Years_Months_Days.txt (text/plain), 11.08 KiB.