Select entries that would fall in a date range 1

[overyde]

Hi,
How do I go about selecting entries that have a start date field and an end date field but not specified in the present month. Bit hard to explain. I'll try to show it:

id-------startdate----------enddate-------
1        2009-04-24         2009-04-26
2        2009-04-25         2009-06-18
3        2009-12-18         2010-01-30

Now How do i call up entries that only take place in May for example?

Reality is built on a foundation of dreams.

 

[towerbase]

There are several ways to do this. Perhaps the clearest is:

SELECT *
  FROM table
 WHERE startdate BETWEEN '2009-05-01' 
                     AND '2009-05-31'
   AND enddate   BETWEEN '2009-05-01'
                     AND '2009-05-31'

Andrew
Hampshire, UK

 

[overyde]

Hi Towerbase -
Unfortunately no records are called as there is no actual entry for May (even though the event runs through May - it neither starts nor ends in May).

Reality is built on a foundation of dreams.

 

[towerbase]

How about

SELECT *
  FROM table
 WHERE statdate <= '2009-05-31'
   AND  enddate >= '2009-05-01'

This should select any entry that has at least one day in May 2009.

Andrew
Hampshire, UK

 

[overyde]

That won't work either as this relies on the data to have an entry actually in May. I have events that run ove a few months ie.
Startdate 2009-04-02
Enddate 2009-06-22

So the event runs all the way through the month of May but is not entered into the dbase as May.

Reality is built on a foundation of dreams.

 

[overyde]

Okay...
For future reference:
SELECT * FROM table WHERE (start_date < end_of_range AND end_date > start_of_range);

Someone...quick...gimme a star


Reality is built on a foundation of dreams.

 

[towerbase]

Which is what I gave you in my second post!!!!

Andrew
Hampshire, UK

 

[overyde]

sorry mate...misread your reply.
Thank you!

Reality is built on a foundation of dreams.