SQL Select users age 17 between date range

Question

Select users who were 17 for a series of dates (from the end date and end date) -

Thanks everyone!

Users

• ID
• Date of Birth

Example:

• User_1 Date of birth 09/28/1996

• User_2 Date of birth 08/25/1996

• User_3 Date of birth 07/28/1995

• User_4 Date of birth 05/25/1995

If the date range is dated 03/05/2013 End date 6/05/2013 * User_1 and User_2 Appear because they meet criteria 17 during this time period *

BUT

If the range is From 02/10/2012 until the end of the date 06/05/2013 All users should appear, as they were all 17 at some point during the date range

I tried using Datepart (), but I can't think it through clearly to get 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 every help I came to the conclusion

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

Answer 1

They must 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 

SQLfiddle for testing with .

Please note that User_1 is executed on September 17, 2013 and User_2 on 08/25/2013, therefore, none of them is (or should be) included in any range.

Answer 2

My preferred method:

Use date data types and explicit comparisons between dates. I recommend storing the date of birth as a date type in SQL Server 2008+, and also use the 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 have moved the dateadd function to constants for this revision. As others have, it means less computation (unless you only had 1 row?) And, perhaps more importantly, it allows you to use the index by date of birth.

BETWEEN Method:

As shown in another answer, using BETWEEN can give 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 fit the entire range, including end points. In this case, we will get a match for any user on their 18th birthday , which is most likely not the desired result (often there is an important age difference from 17 to 18 years). I suppose you could use an extra dateadd to subtract the day, but I like to be consistent in my use 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 a time frame. DATEDIFF shows the difference in terms of crossed boundaries. See how the next age of only one day will show that someone is already a year, because the years are technically one from each other:

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

Calculation using "DATEPART" for many years in the same way will give the same thing (similar to months / 12, etc., up to milliseconds).

Thanks to everyone who noted the possibility of indexing. Let's just not forget the “Make it work, do it right, do it fast” sequence .

Answer 3

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.