Get Current year and next form mysql

Question

Im am selecting various things from a table. The problem is I only want to select them if they belong to the current year and the next year.

My table looks like this

Title Text Date

The date is formated like this 0000-00-00 and is in the format "date"

So the question is how can i select only items from only this year and the next?

Example: the year is 2012, I have items in my table that is old and i dont want them to show - I only want to select items from at the first 2012 1 January and last in this case 31 Dec 2013 current year 2012 + 1 year.

Thanks a lot for any help!

Answer 1

SELECT    *  FROM    table  WHERE    YEAR(date) = YEAR(CURDATE())  OR    YEAR(date) = YEAR(CURDATE()) + 1 

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

Answer 2

SELECT    * FROM   table WHERE   date BETWEEN CONCAT(YEAR(CURDATE()),'-01-01') AND CONCAT(YEAR(CURDATE())+1,'-12-31') 

As ugly as it looks, it allows your query to use index on date field.

Better idea is to create limiting dates in external PHP script, so that the query can use query cache.

---

If you only want to show items, that are no older than two years, you can do this:

SELECT   * FROM   table WHERE   date >= CURDATE() - INTERVAL 2 YEAR;