CODEWITHSUNDEEP
sql-server
Deepak
Deepak

Reputation: 125

Select records by age range

I have following table 

[tblRegistration](
    [FirstName] [nvarchar](100) NOT NULL,
    [MiddleName] [nvarchar](100) NOT NULL,
    [LastName] [nvarchar](100) NOT NULL,
    [DateOfBirth] [date] NOT NULL,
    [DateOfRegistration] [datetime] NOT NULL,

My question is : select all records from table tblRegistration where age between 18 to 25 years.

Thanks!

Upvotes: 2

Views: 3205

Answers (3)

Ruud Helderman
Ruud Helderman

Reputation: 11028

Initially I figured the question was a duplicate of this question, but then I realized OP does not want to list ages; he wants to filter on age. That scenario demands a different approach.

To filter on current age, subtract years from the current date, and compare the result to date of birth.

DECLARE @today date = CAST(GETDATE() AS date)
DECLARE @from date = DATEADD(day, 1, DATEADD(year, -26, @today))
DECLARE @upto date = DATEADD(day, 1, DATEADD(year, -18, @today))

SELECT *
FROM tblRegistration
WHERE DateOfBirth >= @from AND DateOfBirth < @upto

No need to compensate for days and months. It even works if column DateOfBirth happens to be a datetime column that includes the time of birth (i.e. it ignores the time, as most people would).

And yes, it works for leap years. For example, when running the query on 2018-02-28, it will:

  • exclude persons born on 1992-02-28 (are already celebrating their 26th birthday)
  • include persons born on 1992-02-29 (debatable; see https://en.wikipedia.org/wiki/February_29#Born_on_February_29)
  • include persons born on 2000-02-28 (celebrating their 18th birthday)
  • exclude persons born on 2000-02-29 (again, debatable, but at least consistent with 1992-02-29)

If you prefer Taiwanese legislation (i.e. a 'leapling' formally turns 18 on February 28), then please adjust the DECLARE statements as follows.

DECLARE @today date = CAST(GETDATE() AS date)
DECLARE @from date = DATEADD(year, -26, DATEADD(day, 1, @today))
DECLARE @upto date = DATEADD(year, -18, DATEADD(day, 1, @today))

Due to the fact that DateOfBirth is not wrapped inside a function call (as is the case with the DATEDIFF approach), the query optimizer will be able to benefit fully from an index on column DateOfBirth. This may result in a substantial performance improvement under the following conditions:

  • You have an index on DateOfBirth, and this index is likely to be used in the query plan (this may not be the case if there are additional WHERE conditions and joins).
  • The number of index keys is huge. (For a regular non-filtered index, this number equals the number of records in the table.)
  • The number of rows returned is relatively small compared to the number of index keys.

For example, if your table is holding 100M rows, and you expect to retrieve 1% of these, then the amount of reads done by the DATEDIFF approach may actually be a factor 100 higher than in the query above. This is because the query optimizer cannot use an index seek in the DATEDIFF approach; it will fall back on an index scan.

Upvotes: 0

ttwis
ttwis

Reputation: 378

You could use something like this:

SELECT * 
FROM tblRegistration 
WHERE DATEDIFF(yyyy, DateOfBirth, GetDate()) BETWEEN 18 and 25

Upvotes: 1

Robert Synoradzki
Robert Synoradzki

Reputation: 2046

At first I thought DATEDIFF(YEAR, DateOfBirth, GETDATE()) would suffice to get a year difference, but SQL Server computes only a simple int subtraction for both year numbers, so it returns 1 when asked about difference between 2016.12.31 and 2017.01.01.

So, additionally, accounting for months and days must be done:

SELECT *
FROM
(
    SELECT
        *,
        Age = DATEDIFF(yy, DateOfBirth, GETDATE()) -
            IIF(DATEPART(m, DateOfBirth) < DATEPART(m, GETDATE()), 0,
            IIF(DATEPART(m, DateOfBirth) > DATEPART(m, GETDATE()), 1,
            IIF(DATEPART(d, DateOfBirth) > DATEPART(d, GETDATE()), 1, 0)))
    FROM tblRegistration
) x
WHERE Age BETWEEN 18 AND 25

Upvotes: 2

Related Questions