Get Age from Date field in MySQL

Discussion in 'MySQL' started by alexpr07, Mar 5, 2009.

0
  1. #1
    Hello,

    I was wondering if someone can tell me how to get age (in years) from Date field in the MySQL database.

    For example, I need to search the database for a member who is more than 18 and less than 20 years old.

    SELECT * FROM members WHERE date_converted_to_age>18 AND date_converted_to_age<20

    So, what do I write instead of date_converted_to_age?

    Thank you in advance for all your help! :)
     
    alexpr07, Mar 5, 2009 IP
  2. crivion

    crivion Notable Member

    Messages:
    1,669
    Likes Received:
    45
    Best Answers:
    0
    Trophy Points:
    210
    Digital Goods:
    3
    #2
    What format do you have the date? unix timestamp (a long number) or just normal date dd/mm/yyyy ?
     
    crivion, Mar 6, 2009 IP
  3. firemarsh

    firemarsh Peon

    Messages:
    153
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #3
    from http://ma.tt/2003/12/calculate-age-in-mysql/


    
Therefore, this should work with everyone who wasn’t born in the future.

SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age

where dob is date of birth.
    Code (markup):
     
    firemarsh, Mar 6, 2009 IP
  4. alexpr07

    alexpr07 Active Member

    Messages:
    284
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    73
    #4
    The format is DATETIME in MySQL database
     
    alexpr07, Mar 9, 2009 IP