Date comparison problem

[stef686]

I have a website with a diary of events. I have just come across this problem:

The diary is listed on the site month by month - you can click a month and events for that month will show up. Events have a from date and to date and can span across multiple months. This has all been working fine until I added an event running from december 06 to january 07 - it no longer showed up. Here is how my query looked:

(using december 2006 as the selected page as an example)

[code]SELECT * FROM diary WHERE (MONTH(from_date) <= MONTH('2006-12-01') AND MONTH(to_date) >= MONTH('2006-12-01') AND YEAR(from_date) = YEAR('2006-12-01')) OR (MONTH(from_date) <= MONTH('2006-12-01') AND MONTH(to_date) >= MONTH('2006-12-01') AND YEAR(to_date) = YEAR('2006-12-01')) ORDER BY from_date, to_date[/code]

Now looking back it is obvious that this will not work as it is looking for a match in the years. After some research and trial and error I have come up with the following query:

[code]SELECT * FROM diary WHERE (UNIX_TIMESTAMP('2006-12-01') BETWEEN UNIX_TIMESTAMP(from_date) AND UNIX_TIMESTAMP(to_date)) ORDER BY from_date, to_date[/code]

However this is still not working.

This is really bugging me and any help will be greatly appreciated.

Thanks!

[fenway]

That should work... are you sure you're getting the correct output?