2

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.

  • 1
    I don't understand exactly what's the request here. What do you want to show, all the events that happen during the current month? – Marian Feb 4 '11 at 16:06
  • I'm trying to display all events for the current month, and if an event spans over into the next month, it should be included in the current month's display. – Marlon Feb 4 '11 at 16:10
4

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

|improve this 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.

|improve this answer|||||
  • 1
    Unless you wanted to know events that ended during the current month then I would change it to be either startdate between first and last of this month or enddate between first and last day of this month. – jcolebrand Feb 5 '11 at 20:11
1

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)
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.