Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Unanswered: Calculating age in MySQL

    Can someone tell me if there is some function that will take the current year minus a person's date of birth to get their age? If someone could point me towards some info, I would be grateful. I don't see how an aggregate function could do this with the date format.

    Thanks..

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    aggregate functions are used to perform a calculation over the values in a column -- you're right, not the thing for this problem

    let's say you were born on august 13th, 1971

    this is 2007, so 2007 - 1971 = 36, and you are 36 years old today, right?

    wrong
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Code:
    mysql> select  date_format(now(),'%Y') - substring('19630805',1,4) -
            if (date_format(now(),'%m%d') < substring('19630805',5,4),
                    1, 0 ) as age
    +------+
    | age  |
    +------+
    |   43 |
    +------+
    Obviously the 19630805 in this example will need to be a variable (an 8 digit string containing the birthday) and I am mixing types but it's close to what you are after.

    It's a terrible thing to suddenly come across your age.

    Mike
    Last edited by mike_bike_kite; 06-20-07 at 09:42.

  4. #4
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Thanks for the help. I was thinking that maybe the date would be better handled by php.

  5. #5
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    See this tutorial in the mysql manual - http://dev.mysql.com/doc/refman/5.0/...culations.html

  6. #6
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by dbmab
    See this tutorial in the mysql manual - http://dev.mysql.com/doc/refman/5.0/...culations.html
    Thank you dbmab. That certainley seems as though that would do the trick!

Posting Permissions

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