Select entries that fall in date range (part 2)

[overyde]

Hey,
Another issue has raised its ugly head. Man I hate working with date ranges!!!!
I originally had the query:

SELECT * FROM calendar WHERE (startdate < '2009-05-01' AND enddate > '2009-05-30');

And this is some of the data in my database:

id-------startdate----------enddate-------
1        2009-05-02         2009-05-04
2        2009-03-25         2009-09-18
3        2009-12-18         2010-01-30
4        2009-02-22         2009-02-26

Problem is if I want a date range that has a start date before the specified period but still is occuring i.e. a 6 month event and the end date is after the specified period.
i.e. entry 2 if I was searching for events between the period of 2009-05-01 and 2009-08-31. Should be 2 events id 1 and id 2.

Reality is built on a foundation of dreams.

 

[prosper]

If you need to consider it is 6 month event like the date should <2009-11-02, you should write stored procedure. If you want something to be more dynamic and consider lots of condition, you need stored procedure rather than simple sql.

 

[overyde]

Hmmm...that could be a problem. My stored procedure knowledge is pretty dismal

Reality is built on a foundation of dreams.

 

[DonQuichote]

Sorry, I don't understand what you want exactly. But for dates, there are a zillion functions available. From the top of my head, you can use DATE_ADD('2009-05-02', INTERVAL 6 MONTH). Just look up the date functions in the MySQL manual.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[overyde]

Let me try rephrase it:
I’m trying to make an events calendar that can span over a user defined period. i.e. 3months, 6 months, 12 months etc.
If there is an event happening the calendar day(table cell) background changes colour.

Problem is this:
ID 2 starts on the 25-03-09 and ends on the 18-09-2009.
How do I create a select query for a date range say from the 1st April to 30th August?
There should be 2 rows for the dates below.

id-------startdate----------enddate-------
1        2009-05-02         2009-05-04
2        2009-03-25         2009-09-18
3        2009-12-18         2010-01-30
4        2009-02-22         2009-02-26

Reality is built on a foundation of dreams.

 

[DonQuichote]

Ah. You can go to the start of a month by subtracting the day part of the date. So use the DATE() function to remove the time parts (if there are any), EXTRACT() to get the day part and DATE_SUB() to to the math. Like this:

SET @InputDate=NOW(); -- Date and Time
SET @StartDate=DATE_SUB(DATE(@InputDate), INTERVAL EXTRACT(DAY FROM @InputDate) DAY);
SELECT @InputDate,@StartDate;

This will show a date-and-time value and a date value of the last day of last month (because day zero does not exist in a month and we subtracted all the days). This last value can be the lower bound for your SELECT statement. You can get this for a month later by similar means.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)