Selecting records according to date

Hi there,

I'm having trouble selecting events from my database appropriate to a specific month and year of the user's choice.

Suppose for the following examples, the user wishes to view all records where the event is during, or overlaps into, April 2007.

The following query produces results:

SELECT tblevents.*, DATE_FORMAT(tblevents.event_expires,'%d.%m.%Y') as event_expires, DATE_FORMAT(tblevents.event_to,'%d.%m.%Y') as event_to, DATE_FORMAT(tblevents.event_from,'%d.%m.%Y') as event_from FROM tblevents WHERE (tblevents.event_from BETWEEN '2007/04/01' AND '2007/04/30') OR (tblevents.event_to BETWEEN '2007/04/01' AND '2007/04/30') ORDER BY event_from ASC

However, this version does not:

SELECT tblevents.*, DATE_FORMAT(tblevents.event_expires,'%d.%m.%Y') as event_expires, DATE_FORMAT(tblevents.event_to,'%d.%m.%Y') as event_to, DATE_FORMAT(tblevents.event_from,'%d.%m.%Y') as event_from FROM tblevents WHERE ('2007/04/01' BETWEEN tblevents.event_from AND tblevents.event_to) OR ('2007/04/30' BETWEEN tblevents.event_from AND tblevents.event_to) ORDER BY event_from ASC


The first version works largely, but if an event spans from say January - June, it won't return such rows because the to and from date column values fall outside the current month. I figured I could fix this using the 2nd query shown about, but that doesn't seem to want to return any rows at all. I guess the BETWEEN syntax doesn't take date columns as the arguments in that way? Why, I can't understand though.

Can someone please tell me how to fix this? I know I could adjust my system to record an event_id against a date in a new table, but I doubt there's any need for that and some nice SQL should yield the results I require...can it?

Hope you can help!

Many thanks.