Jump to content


Photo

Date comparison problem


  • Please log in to reply
1 reply to this topic

#1 stef686

stef686
  • Members
  • Pip
  • Newbie
  • 5 posts

Posted 03 August 2006 - 03:04 PM

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)

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

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:

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

However this is still not working.

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

Thanks!

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 August 2006 - 03:49 PM

That should work... are you sure you're getting the correct output?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users