how to create QUERY to know the Current Age INCLUDING YEAR & MONTH?

magfiroh · #1 (**permalink**) 06-18-08, 20:45

I have mysql, like this :
-------------------------
CREATE TABLE `EMPLOYEE` (
`ID` int(11) NOT NULL auto_increment,
`NAME` varchar(100) default NULL,
`BIRTHDAY` datetime default NULL
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
------------

my question is :
how to create QUERY to know the Current Age for each employee INCLUDING YEAR AND MONTH? it's look like this 40.11 (40 is YEAR and 11 is MONTH)

THANK

r937 · #2 (**permalink**) 06-18-08, 23:23

1. create a testing table:

Code:

CREATE TABLE somedates
( adate DATE NOT NULL PRIMARY KEY
);

2. populate it with a comprehensive set of test dates

3. run this query:

Code:

SELECT adate
     , FLOOR(mths/12)           AS age_years
     , mths - 12*FLOOR(mths/12) AS age_months
 FROM ( SELECT adate
             , PERIOD_DIFF(
                 EXTRACT(YEAR_MONTH FROM 
                   DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH))
                ,190001) 
             - PERIOD_DIFF(
                 EXTRACT(YEAR_MONTH FROM 
                   adate)
                ,190001) 
             + CASE WHEN DAY(adate) <= DAY(CURRENT_DATE) 
                    THEN 1 ELSE 0 END
                AS mths
        FROM somedates ) AS dt
ORDER 
    BY adate

vwalah!!

Internet Services

Web Development

Digital Marketing

Consumer Tech