mySQL DATETIME select in between dates

Question

I have a mySQL table named events with 4 fields (event_id, event_name, event_start_date, event_end_date)

The problem is retrieving the correct events

I would get the record for all the active events in a time period for example between 2011/03/01 to 2011/03/30

event 1 starts at 2011/03/10 and ends 2011/03/20 (start and ends inside)
event 2 starts at 2011/02/05 and ends 2011/03/23 (starts before and ends inside)
event 3 starts at 2011/03/25 and ends 2011/05/01 (starts inside and ends after)
event 4 starts at 2011/01/25 and ends 2011/10/12 (starts before and ends after)

All events are active during the considered time period and should be retrieved as records

I have no idea how to make it work correctly! Solutions? Suggestions? Ideas?

Thanks

[This question has a similar answer which applies.][1] Mark Buyers has given the specific answer for this question. [1]: http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap — Paul Morgan, Nov 27 '11 at 20:27

score 3 · Accepted Answer · answered Nov 27 '11 at 20:10

3

Assuming @Start and @End hold the date range you are looking for:

SELECT * FROM Events
  -- Exclude items known to be outside the range
WHERE NOT ((event_end_date < @Start) OR (event_start_date > @End))

answered Nov 27 '11 at 20:10

competent_tech

44,465
11
90
113

You're welcome. Don't forget, if any answer solves your question, you should click on the checkbox next to that answer to let future visitors to your question know that it was the answer that fixed your problem. – competent_tech Nov 27 '11 at 21:04

Mark Byers · Answer 2 · 2011-11-27T20:41:12.057

2

Try this:

SELECT
    event_id,
    event_name,
    event_start_date,
    event_end_date
FROM events
WHERE event_start_date <= '2011-03-30'
AND event_end_date >= '2011-03-01'

edited Nov 27 '11 at 20:41

answered Nov 27 '11 at 20:06

Mark Byers

811,555
193
1,581
1,452

that would only return the event 1 that starts at 2011/03/10 and ends 2011/03/20 (start and ends inside) but not the others – GAVELLO Nov 27 '11 at 20:35
@GAVELLO: Why do you think that? Are you just guessing or did you try it? If you tried it, please post the exact query you tried. – Mark Byers Nov 27 '11 at 20:39
//$sql = 'SELECT * FROM '. self::$table_name .' WHERE event_start_date >= "'. $start_date .'" AND event_end_date <= "'. $end_date.'"' ; – GAVELLO Nov 27 '11 at 20:43
WHERE event_start_date <= '2011-03-30' AND event_end_date >= '2011-03-01' (like you wrote) returns only the event 3 and 4 but not 1 and 2 – GAVELLO Nov 27 '11 at 20:52
1

@GAVELLO: Why did you change the query? I wrote `WHERE event_start_date <= '2011-03-30' AND event_end_date >= '2011-03-01'` and you changed it to `WHERE event_start_date >= '2011-03-01' AND event_end_date <= '2011-03-30'`. Obviously if you write something completely different without understanding what you are doing then it's not going to work, is it? – Mark Byers Nov 27 '11 at 21:32

score 0 · Answer 3 · answered Nov 27 '11 at 20:06

0

Select * From temp where mydate >= '2011-03-10' and mydate =< '2011-03-20';

and so on

answered Nov 27 '11 at 20:06

abcde123483

3,885
4
41
41

Not so easy this would return only the events tat starts and ends inside the time period. – GAVELLO Nov 27 '11 at 20:54

mySQL DATETIME select in between dates

3 Answers3