[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

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

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 ?
1 Solution
depends on what type of database you use

in msaccess for instance you can use:

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

RanLevyAuthor Commented:
I was refering to msaccess, and this solution worked (but only after I added # before and after the DateSerial result).
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())
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 ;-)
Forced accept.

EE Admin

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now