CODEWITHSUNDEEP
mysqlsqldatetime
GAVELLO
GAVELLO

Reputation: 123

mySQL DATETIME select in between dates

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

  1. event 1 starts at 2011/03/10 and ends 2011/03/20 (start and ends inside)
  2. event 2 starts at 2011/02/05 and ends 2011/03/23 (starts before and ends inside)
  3. event 3 starts at 2011/03/25 and ends 2011/05/01 (starts inside and ends after)
  4. 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

Upvotes: 4

Views: 10222

Answers (3)

Mark Byers
Mark Byers

Reputation: 837986

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'

Upvotes: 2

competent_tech
competent_tech

Reputation: 44921

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))

Upvotes: 3

abcde123483
abcde123483

Reputation: 3905

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

and so on

Upvotes: 0

Related Questions