SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    Orlando, FL
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting people with a certain age range based on dob

    I have a table named 'users' with three columns, set up like so:

    id / fullname / dob
    01 / john smith / 1985-01-20
    02 / betty jackson / 1988-05-30
    03 / nick anthony / 1979-09-02
    etc...

    Using a "SELECT * from users WHERE..." query, is there a way to display users with a certain age range with the current database structure? For instance, can I query the database for people who are between 18 and 22 years old? Or do I have to modify the database in some way?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, you can do it easily, all you need is an expression which calculates age
    Code:
    select fullname
         , dob
         , year(current_date) 
           - year(dob)
           - case when month(current_date)
                     > month(dob)      
                  then 0
                  when month(current_date)
                     < month(dob)      
                  then 1
                  when dayofmonth(current_date)
                     < dayofmonth(dob) 
                  then 1
                  else 0 end    as age
      from users
    once you've seen that these results are actually correct, then you can simply use the expression in a WHERE clause
    Code:
    select fullname
         , dob
      from users
     where year(current_date) 
           - year(dob)
           - case when month(current_date)
                     > month(dob)      
                  then 0
                  when month(current_date)
                     < month(dob)      
                  then 1
                  when dayofmonth(current_date)
                     < dayofmonth(dob) 
                  then 1
                  else 0 end 
             between 18 and 22
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    Orlando, FL
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Whoa, thanks for the help! I was trying to figure it out for like 2 hours last night and there's no way I would have found my way to that conclusion. Thanks again!


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
  •