Getting age in years in a SQL query

Question

Hello I've been tasked with doing a few queries on a large SQL Server 2000 database.

The query I'm having trouble with is "find the number of people between ages 20 and 40"

How would I do this? My previous query to get a count of everyone looks like this:

select count(rid) from people where ... 

(with the ... being irrelevant conditions). I've googled some but the only thing I've found for calculating age is so large that I don't see how to embed it into a query, or it is a stored procedure which I do not have the permissions to create.

Can someone help me with this?

The relevant part of the people table is like so:

RID(unique key) | dateofbirth(datetime) | firstname....

Answer 1

I'm assuming the people table has birth dates in it.

Rough pseudo code.

Select DateDiff(YY, people.DOB. Datetime.NOW) as age
from people
where age....

Answer 2

Assuming birthday is stored as a DateTime

Select Count(*)
From    (
        Select Id, Floor(DateDiff(d, BirthDate, GetDate()) / 365.25) As Age
        From People
        ) As EmpAges
Where EmpAges Between 20 And 40

This could also be written without the derived table like so:

Select Count(*)
From People
Where Floor(DateDiff(d, BirthDate, GetDate()) / 365.25)  Between 20 And 40

Yet another way would be to use DateAdd. As OMG Ponies and ck mentioned, this one would be the most efficient of the bunch as it would enable the use of an index on dateOfBirth if it existed.

Select Count(*)
From People
Where DateOfBirth Between DateAdd(yy, -40, GetDate()) And DateAdd(yy, -20, GetDate())

Answer 3

As you always express age as an integer...

select cast(DateDiff(d, people.dob, GetDate()) / 365.25 as int) As Age 

Answer 4

You could do something like: YEAR(GETDATE()) - YEAR(dateOfBirth) > 20

Answer 5

You should compute the dates that form the boundaries of your range, and then use those dates.

DECLARE @Today datetime, @StartDate datetime, @EndDate datetime

SET @Today = DateAdd(dd, DateDiff(dd, 0, GetDate()), 0)
SET @StartDate = DateAdd(dd, 1, DateAdd(yy, -40, @Today))
SET @EndDate = DateAdd(yy, -20, @Today)

SELECT *
FROM People
WHERE DateOfBirth BETWEEN @StartDate AND @EndDate

This gives you a query where you have a chance at using an index.

Answer 6

select count(*)
from YourTable
where dateofbirth >= '1970-05-24' and dateofbirth <= '1990-05-24'

Adjust the dates according to the current date.