Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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

3 Answers 3

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

share|improve this answer
    
+1 for the ASCII art! Oldies but goldies! –  Marian Feb 5 '11 at 8:02

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.

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

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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