Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Posts
    14

    Unanswered: Age Calculations

    I have a table of Users on a MySQL database, which includes a field, BirthDate.

    I would like to be able to:
    1. Calculate the User's age, based on the BirthDate, to return in a query, and
    2. Filter by that calculated age (i.e.: Find everyone from 18-25).

    I have found various examples of returning the age in a query, but I haven't found the best way to filter by age, age range, etc.

  2. #2
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    A HAVING clause should work. The following is an expansion of the PET tutorial example in the msyql manual (untested but should work) -

    SELECT name, birth, CURDATE(),
    (YEAR(CURDATE())-YEAR(birth)) - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
    AS age
    FROM pet
    HAVING age >= 18 AND age <= 25

    You could also put the age calculation into a WHERE clause with the comparisons.

  3. #3
    Join Date
    Dec 2005
    Posts
    14
    Thanks. I originally tried it using a WHERE clause on the age alias, and it didn't work. I tried your example with the HAVING clause, and it did the trick.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do you undestand what this is doing? --
    Code:
     ... - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
    if not, i would suggest that you re-write it using a CASE expression, because it won't work in any other database written like that
    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
  •