SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    My precious!!! astericks's Avatar
    Join Date
    Mar 2002
    Location
    Vancouver, BC
    Posts
    1,971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Calculating age - How's this one?

    I'm just wondering if this select statement is good for age calculation:

    Note : dob is date of birth in the format YYYY-MM-DD

    Code:
    SELECT YEAR(curdate()) - YEAR(dob) - IF(dayofyear(curdate()) < dayofyear(dob),1,0) as age from tbl_name
    Only situation it chokes is when dayofyear(dob) = dayofyear(curdate()).

    So, is this one ok, or is there something I overlooked? Also, if htere's a better one, can anyone post it here?

    cheers
    asT.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    that's as good as it gets

    there's something almost like it in the mysql docs
    http://www.mysql.com/doc/en/Date_calculations.html

    except that they use RIGHT(CURRENT_DATE,5) which obviously depends on character conversion to yyyy-mm-dd format and they are comparing the mm-yy strings

    yours is numeric, and more robust

    also, when dayofyear(dob) = dayofyear(curdate()), i would not say this chokes, i would say it gives the right answer -- on your birthday, you can go ahead and say you're a year older

    rudy
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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