Ensure that a start and end date are within a selected range

Question

I have the following SQL query

SELECT        
    users.username, users.first_name, users.last_name, users.description, 
    sprints.sprint_start_date, sprints.sprint_end_date, users.user_id
FROM          
    sprints 
INNER JOIN 
    sprints_vs_users ON sprints.sprint_id = sprints_vs_users.sprint_id 
RIGHT OUTER JOIN 
    users ON sprints_vs_users.user_id = users.user_id
WHERE     
    (sprints.sprint_start_date BETWEEN CONVERT(datetime, @startdate, 103) 
               AND CONVERT(datetime, @enddate, 103)) 
    AND (sprints.sprint_end_date BETWEEN CONVERT(datetime, @startdate, 103) 
               AND CONVERT(datetime, @enddate, 103))

If we were to declare @startdate variable as 26/11/2015 and declare @enddate variable as 03/12/2015 and if we use an example of a sprint_start_date of 27/11/2015 and sprint_end_date of 4/12/2015

I want the sprint with those sprint start and end dates to appear in the result as the sprint dates occur during the selected variable dates. However at the moment, the query does not display this result which I think is due to the sprint_end_date not appearing between the 26/11/2015 and 03/12/2015 although I am not sure how to fix this, so any help would be much appreciated.

Please tag dbms used. Too many products are far from ANSI SQL compliant when it comes to date/time. — jarlh, Nov 25 '15 at 19:40
If you are looking for some of the dates between `sprint_start_date` and `sprint_end_date` to be within the defined date range (as opposed to all of them which is what your query is doing now), just use `OR` instead of `AND`. — SunKnight0, Nov 25 '15 at 19:41
When right outer join, put the left side tables' conditions in the ON clause to get true outer join result. (When in WHERE, you get regular inner join result.) — jarlh, Nov 25 '15 at 19:41
@SunKnight0 That will work for the variables I have listed. Although, if the `@startdate` variable is `1/12/2015` and `@enddate` variable is `03/12/2015`, they will not appear despite being within the two sprint start date ranges. Any insight into how to remedy this? — mcclosa, Nov 25 '15 at 19:49
Sure, the way I would check is sprint_start_date<=@enddate AND sprint_end_date>=@startdate — SunKnight0, Nov 25 '15 at 19:52
Possible duplicate of [Determine Whether Two Date Ranges Overlap](http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap) best explanation ever — Juan Carlos Oropeza, Nov 25 '15 at 19:53
@SunKnight0 Again, it seems to work one way but not the other. if the `@startdate` variable is `1/12/2015` and `@enddate` variable is `03/12/2015` that query works fine but if I have it the original way off if the `@startdate` variable is `26/11/2015` and `@enddate` variable is `03/12/2015` it does not work... maybe I have written the query incorrectly, could you double check `(sprints.sprint_start_date <= CONVERT(datetime, @enddate, 103)) AND (sprints.sprint_end_date >= CONVERT(datetime, @startdate, 103))` — mcclosa, Nov 25 '15 at 20:02
@SunKnight0 My apologies, your query was correct, thank you. The query builder I was using had swapped the variables in the input list and didn't notice. — mcclosa, Nov 25 '15 at 20:16
@JuanCarlosOropeza That was a really great explanation, really helped me understand the query. — mcclosa, Nov 25 '15 at 20:17

score 0 · Answer 1 · answered Nov 25 '15 at 20:21

Your existing logic means that the query returns only sprints where the start date AND the end date fall within the period defined by the two variables (i.e. the entire duration of the sprint falls within the period).

It sounds like what you actually want is to return sprints where either the start date or the end date fall within the defined period (i.e. the sprint duration is entirely or partially within the defined period) OR the sprint starts and ends either side of the defined period (i.e. the defined period falls within the duration of the sprint).

If so, you can modify your query like this:

SELECT        
    users.username, users.first_name, users.last_name, users.description, 
    sprints.sprint_start_date, sprints.sprint_end_date, users.user_id
FROM          
    sprints 
INNER JOIN 
    sprints_vs_users ON sprints.sprint_id = sprints_vs_users.sprint_id 
RIGHT OUTER JOIN 
    users ON sprints_vs_users.user_id = users.user_id
WHERE 
(    
    (sprints.sprint_start_date BETWEEN CONVERT(datetime, @startdate, 103) 
               AND CONVERT(datetime, @enddate, 103)) 
    OR 
    (sprints.sprint_end_date BETWEEN CONVERT(datetime, @startdate, 103) 
               AND CONVERT(datetime, @enddate, 103))
) 
OR
(
    (sprints.sprint_start_date <= CONVERT(datetime, @startdate, 103)) 
    AND
    (sprints.sprint_end_date >= CONVERT(datetime, @enddate, 103))
)

Ensure that a start and end date are within a selected range

1 Answers1