Reputation: 125
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
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:
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:
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).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
Reputation: 378
You could use something like this:
SELECT *
FROM tblRegistration
WHERE DATEDIFF(yyyy, DateOfBirth, GetDate()) BETWEEN 18 and 25
Upvotes: 1
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