Find a date between start_date and end_date

Question

I have a table of events with a recorded start and end time. I want to find all events that occur on a specific date. Some events started a year ago and some will continue farther ahead.

I would like to be able to pick for example May 20, 2010 and find all events occurring on that date.

Answer 1

SELECT * FROM yourTable 
  WHERE start_date >= '2010-01-01' AND end_date <= '2010-06-30'

This query will return all dates where start_date is on or after 1st Jan, 2010, and end_date is on or before 30th June, 2010.

Answer 2

Assuming you have input parameters named @start_date and @end_date, and you want to find any event that overlaps that range:

select *
  from TableName
 where @start_date between start_date and end_date
    or @end_date between start_date and end_date

Answer 3

select * from TableName
where '2010-05-20' >= start_date and '2010-05-20' <= end_date

This will give you all events that occur on a specific date, even if the start or send dates are on different days.

Answer 4

Try...

WHERE (((MONTH(start_date) >= MONTH(NOW())) && (MONTH(end_date) <= MONTH(NOW()))) && YEAR(start_date) >= YEAR(NOW()))

That would get you anything from 3/2010.

Or..

WHERE ((MONTH(start_date) >= MONTH(NOW())) && YEAR(start_date) >= YEAR(NOW())))

Would get you events after 3/2010 into the future, and nothing before that.

Or..

WHERE (start_date_var) BETWEEN start_date AND end_date

Simply, where (passed value) is between any event start and end date.

These should produce results. It depends on how you are executing the query. Are you accepting form/url arguments to change dates?

It's confusing though that you want to have all dates that are not expired, yet also want dates that occurred on a specific date. Where are you getting start_date from?