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]: stackoverflow.com/questions/325933/… — Paul Morgan
– Paul Morgan, Commented Nov 27, 2011 at 20:27

competent_tech · Accepted Answer · 2011-11-27 20:10:29Z

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, 2011 at 20:10

competent_tech

45.1k12 gold badges96 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

competent_tech Over a year ago

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.

Mark Byers · Accepted Answer · 2011-11-27 20:41:12Z

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, 2011 at 20:41

answered Nov 27, 2011 at 20:06

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

5 Comments

GAVELLO Over a year ago

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

Mark Byers Over a year ago

@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.

GAVELLO Over a year ago

//$sql = 'SELECT * FROM '. self::$table_name .' WHERE event_start_date >= "'. $start_date .'" AND event_end_date <= "'. $end_date.'"' ;

GAVELLO Over a year ago

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

Mark Byers Over a year ago

@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?

abcde123483 · Accepted Answer · 2011-11-27 20:06:58Z

0

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

and so on

answered Nov 27, 2011 at 20:06

abcde123483

3,8954 gold badges43 silver badges41 bronze badges

1 Comment

GAVELLO Over a year ago

Not so easy this would return only the events tat starts and ends inside the time period.

Collectives™ on Stack Overflow

mySQL DATETIME select in between dates

3 Answers 3

1 Comment

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related