I found a useful MySQL trick which would have saved me some time a when making a site last year. It’s actually a fairly standard procedure but if you’re learning as you go it’s not exactly obvious. It goes something like this (date_col is a sql date column):
SELECT * FROM table WHERE YEAR(date_col) = 2009
You can probably guess what it does – it gets all records which are dated 2009. You can do MONTH() and DAY() as well.
You can use it in different ways, too. How about if you’ve got a load of news items and you want to know which year the first one was posted in? You do this:
SELECT YEAR( news_date ) AS 'year' FROM `news` ORDER BY news_date LIMIT 1
Or if you want the year of the latest one, this:
SELECT YEAR( news_date ) AS 'year' FROM `news` ORDER BY news_date DESC LIMIT 1