Monday, 20 May 2013

Retrieve records between dates

Retrieve records between dates

I have the following query which will retrieve events that are between the start and end dates supplied as parameters:
SELECT * FROM events
WHERE (startDate BETWEEN CAST(@start AS DATETIME) AND CAST(@end AS DATETIME)
OR endDate BETWEEN CAST(@start AS DATETIME) AND CAST(@end AS DATETIME))
This works fine for retrieving events which overlap the start and end times, but will not get events which are fully encapsulated between those times. A picture might make this clearer:
Event A |------------------------------------------------------------------| Event B ----------------------|------------|-------------------------------
Where | represents a start or end date
In my picture, using my query with the start and end times for Event B, it would NOT retrieve Event A.
How do I change the query so that it will show all overlapping and encapsulated events?

No comments:

Post a Comment