Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2008
    Posts
    3

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

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •