Selecting an entry that spans adjacent months

Question

So i have a database table that contains the a "event name", "start-date", "end-date" columns which is used in a calendar application im working on.

The events get shown one month at a time, and that is working great, except that it does not return any events that span over into an adjacent month. For example, an event that runs from 2/25 - 3/05.

I will only get events for the current month, provided the end-date is also within the month.

I know that this has to do with the SQL i'm writing. This is the sql that I currently have:

SELECT * FROM Events WHERE startdate >= '2/1/2011' AND enddate < '3/1/2011' ORDER BY StartDate

The dates above are dynamic.

Answer 1

I keep the following bit of ASCII art around that I drew a few years back as reference for dealing with matching ranges, reformatted, as it was originally in POD; in your case, you're using the second one, which only matches example 3:

Interval Matching

 .........A============B......... Search Range
 ...x--y..|............|......... (never match)
 .........|............|...x--y.. (never match)
 ......x--|--y.........|......... (1)
 ......x--|------------|---y..... (2)
 .........|...x-----y..|......... (3)
 .........|..........x-|----y.... (4)

• Intersection ... matches examples 1,2,3,4

  A <= y && B >= x

• (item fully contained by search range) ... matches example 3 only.

  A <= x && B >= y

• (item fully includes search range) ... matches example 2 only.

  A >= x && B <= y

Answer 2

I would not use the end date and just rely on start date. Theoretically, if you had an event last months, then as long as the start date was in February, you would be fine.

Answer 3

Answered my own question. I landed up using the DATEPART() T-SQL Function to help me out. Here is an example of what I was trying to do:

declare @currentmonth as int = Month('3/1/2011')
declare @prevmonth as int = Month('3/1/2011') - 1

select * from Calendar where DATEPART(M, StartDate) = @currentmonth or (DATEPART(M, StartDate) = @prevmonth AND DATEPART(M, EndDate) = @currentmonth)