How do I select records with a given date type field older than a specific date ?

Posted on 2007-09-29
Last Modified: 2010-03-20
I Have a table of persons that includes date of birth.
I want to select people of a specific age (e.g. - over 40).
What is the correct SQL syntax ?
Question by:RanLevy
    LVL 19

    Accepted Solution

    depends on what type of database you use

    in msaccess for instance you can use:

    SELECT *
    FROM yourtable
    where yourfieldwithdateofbirth<DateSerial(Year(Now())-40,Month(Now()),Day(Now()))


    Author Comment

    I was refering to msaccess, and this solution worked (but only after I added # before and after the DateSerial result).
    LVL 51

    Expert Comment

    Depends on what you mean by over 40.  This would give you people that are at least 40 years old.

    WHERE dateOfBirthColumn <= dateAdd('yyyy', -40, date())
    LVL 51

    Expert Comment

    I have no problem with Limbeck's suggestion, but again it depends on what the asker meant by over 40.  

    In terms of age, "over" often means X years of age old OR older.  If that's what was meant, they should use the code in my suggestion.  It finds people 40 years old or older.  If they want to find individuals that are 40 years old _plus_  1 day they should use Limbeck's suggestion.

    Of course if they want people that are 41 years of age or older, both suggestions are wrong ;-)
    LVL 1

    Expert Comment

    Forced accept.

    EE Admin

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now