title: MySQL - convert and compare dates on query
body: <p>I'm working on a football site which, along with many features, lists result tables. The tables are separated between seasons (season name, etc.) and rounds (which are generated every time a new season starts up, like 2008/2009, 2009/2010, etc.)</p>
  <p>rounds table has a start date and a end date, and the only way I have to dynamically know which round I should work with in the website frontend is by comparing those dates with the remaining rounds regarding the same season.</p>
  <p>All the data is fetched from a xml, and the dates look like this :</p>
  <pre><code>2009-08-16
  </code></pre>
  <p>So, my 2 problems :</p>
  <ul>
  <li>compare dates (and probably convert them on-the-fly) and find out which round has the biggest end date;</li>
  <li>get the right round (some future rounds may be already in the database but the current round could be another prior to those, if you know what I mean)</li>
  </ul>
  <p>I'm not expert or anything with SQL, so I'd like to know how would you guys do that.</p>
  <p>Here's my current query :</p>
  <pre><code>SELECT seasons.competition_id AS cid, 
         seasons.id AS sid, 
         rounds.id AS rid, 
         seasons.start_date AS sstart, 
         seasons.end_date AS send, 
         rounds.start_date AS rstart, 
         rounds.end_date AS rend, 
         round_groups.id AS rgid 
    FROM seasons 
  INNER JOIN rounds ON seasons.id=rounds.season_id 
  LEFT JOIN round_groups ON rounds.id=round_groups.round_id
  </code></pre>
  <p>ps: you may notice the round_groups table that I forgot to mention, it's used when a certain season is divided in groups, like UEFA and Champions.</p>
  <p>EDIT :</p>
  <p>A season is a entry on the database that is updated once per season, because some competitions change their name regarding the sponsor of the season. The round changes once per season at least, but may have more changes. In certain competitions like UEFA, they have some first elimination rounds, then a group phase, and then elimination again, which would result in 3 different rounds. The final output should be a list of every round currently active regarding each season.</p>