How can I SELECT an 'entry' that spans adjacent months?

Question

I have an Event table (used in a calendar application I am working on) that holds the following columns:

• EventName
• StartDate
• EndDate

The events stored in said table 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 EndDate is also within the month under consideration.

I know that this has to do with the statements I am writing. This is what 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)