Finding date spans within a date span (am I doing this correctly)

Databases
1

I have a calendar, eventStart and eventEnd are the two key columns.

I want to show event within the current month being shown. The following is what I came up with for the search statement (between statements are being generated by application server). Is this correct?


SELECT *
FROM events 
WHERE ( events.calendarId = ? ) 
AND (eventStart BETWEEN {ts '2011-06-01 00:00:00'} AND {ts '2011-06-30 00:00:00'} 
AND eventEnd BETWEEN {ts '2011-06-01 00:00:00'} AND {ts '2011-06-30 00:00:00'})

thanks for any critiques/advice

2

you’ve missed a couple of scenarios

have a look at this thread – http://www.sitepoint.com/forums/databases-88/checking-number-range-contained-within-range-under-sql-664040.html

your problem is practically identical

:slight_smile:

3

on one hand, i appreciate the help… on the other i’m smacking myself in the face for not thinking of that.

Simple answers are always the most painful for me to receive (reason mentioned above). :slight_smile:

Thanks