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 lik开发者_开发百科e to be able to pick for example May 20, 2010 and find all events occurring on that date.
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.
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.
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?
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
精彩评论