CODEWITHSUNDEEP
sqlsql-server-2008
user196830
user196830

Reputation: 55

SQL Select Users Who age is 17 between Date Range

Select Users Who were 17 During a range of dates (From and End Date)--

Thanks everyone!

Users

Example:

  1. User_1 Birthdate 09/28/1996

  2. User_2 Birthdate 08/25/1996

  3. User_3 Birthdate 07/28/1995

  4. User_4 Birthdate 05/25/1995

If Range of dates are FROM 03/05/2013 To End Date 6/05/2013 *User_1 and User_2 Appear because they meet the criteria of being 17 during that time period*

HOWEVER

If Range is From 02/10/2012 and To End Date 06/05/2013 All the Users should appear since they all were 17 at some point during the range of date

I have tried using the Datepart() but i can't clearly think it through to derive at the answer I want

Select u.id, u.birthdate
From Users u
where convert(varchar,DATEPART(MM,u.birthdate))<=DATEPART(MM,'03/05/2013')

With everyone help I came to a conclusion

DATEADD(YY,17,birthdate) between @from and @end

Upvotes: 4

Views: 6991

Answers (3)

Tim Lehner
Tim Lehner

Reputation: 15261

My Preferred Method:

Use date datatypes and explicit comparisons between dates. I recommend storing birthdate as a date datatype in SQL Server 2008+, and also using ISO 8601 format for datetime literals to avoid ambiguity.

select id, birthdate
from Users
where birthdate > dateadd(year, -18, '2013-03-05') -- Check lower bounds
    and birthdate <= dateadd(year, -17, '2013-06-05'); -- Check upper bounds

Note that I've moved the dateadd function to the constants for this revision. As keenly observed by others, this means less calculation (unless you only had 1 row?), and -- perhaps more importantly -- allows for usage of an index on birthdate.

The BETWEEN Method:

As shown in another answer, using BETWEEN can yield a similar result:

select id, birthdate
from users 
where birthdate between dateadd(year, -18, '2013-03-05')
        and dateadd(year, -17, '2013-06-05')

However, BETWEEN is inclusive, meaning it will match on all of the range including the end points. In this case, we would get a match on any user's 18th birthday, which is most likely not the desired result (there is often an important difference between ages 17 and 18). I suppose you could use an additional DATEADD to subtract a day, but I like to be consistent in my my usage of BETWEEN as Aaron Bertrand suggests.

What Not To Do:

Do not use DATEPART or DATEDIFF for this type of comparison. They do not represent timespans. DATEDIFF shows the difference in terms of boundaries crossed. See how the following age of just one day would show someone as being a year old already, because the years are technically one apart:

select datediff(year, '2012-12-31', '2013-01-01'); -- Returns 1!

A calculation using 'DATEPART' for years in this fashion would yield the same thing (similarly with months/12, etc., all the way to milliseconds).

Thanks to all who noted the indexing possibility. Let's just not forget the sequence of "Make it work, make it right, make it fast."

Upvotes: 2

Joachim Isaksson
Joachim Isaksson

Reputation: 181087

These should do it;

SELECT * FROM users 
WHERE birthdate BETWEEN DATEADD(year, -18, '2013-03-05')   -- lo date of range
                    AND DATEADD(year, -17, '2013-06-05');  -- hi date of range

SELECT * FROM users 
WHERE birthdate BETWEEN DATEADD(year, -18, '2012-02-10')   -- lo date of range
                    AND DATEADD(year, -17, '2013-06-05');  -- hi date of range

An SQLfiddle to test with.

Note that User_1 turns 17 on 09/28/2013 and User_2 on 08/25/2013, so neither of them is (or should be) included in either range.

Upvotes: 4

armitage
armitage

Reputation: 192

Try

SELECT * 
FROM
Users
WHERE
        DATEDIFF(year,@DATE1, '02/10/2012') = 17
OR
    DATEDIFF(year,@DATE2, '06/05/2013') =17

Replacing @DATE1 and @DATE2 above with the required dates.

Upvotes: -1

Related Questions