Syntax for BETWEEN two dates

Newbie question . . .

[Wonderstuff]

Any assistance please. I must be close!

I have a table which contains:

An event e.g. A Circus
datefrom e.g. 2007-03-01
dateto e.g. 2007-05-31

If I want to call events by month e.g. April datefrom / dateto I have problems with:

SELECT * FROM table WHERE datefrom>='2007-04-01' OR dateto<='2007-04-30';

Nothing is selected because 04/April is BETWEEN the datefrom and dateto.

How would I rewrite the query using BETWEEN?

Many thanks W.

[LifeinAsia]

Try this:
SELECT *
FROM table
WHERE datefrom<='2007-04-30' AND dateto>='2007-04-1'

[syber]

SELECT *
FROM table
WHERE datefrom BETWEEN '2007-04-01' AND '2007-04-30';

[LifeinAsia]

Syber,
Look at the example data- it won't be selected.

[syber]

True, but it is still the right answer.

If he doesn't want April dates then he would write:

SELECT *
FROM table
WHERE datefrom NOT BETWEEN '2007-04-01' AND '2007-04-30';

[phranque]

try this:

SELECT *
FROM table
WHERE datefrom BETWEEN '2007-04-01' AND '2007-04-30'
OR dateto BETWEEN '2007-04-01' AND '2007-04-30';

[phranque]

[edit]delete cat-submitted repeat...[/edit]

[mattur]

Or, to avoid messing about with dates:

SELECT *
FROM table
WHERE (MONTH(datefrom) = 4 AND YEAR(datefrom) = 2007)
OR (MONTH(dateto) = 4 AND YEAR(dateto) = 2007);

[Wonderstuff]

Thanks to everyone that has made suggestions. Your help is really appreciated.

I think that the trick here is to SELECT APRIL/04 events without using APRIL/04 in the code. For example, if a different event(let's say a widget exhibition) ran from 2007-01-01 until 2007-08-31 it would be available in APRIL/04 as well as the reamining seven months.

So the winner is . . . .Lifeinasia.

Thanks again to everyone. The thinking behind your suggestions has been an inspiration.

[LifeinAsia]

Whoo-hoo!