CODEWITHSUNDEEP
phpmysql
Tobias
Tobias

Reputation: 494

Get Current year and next form mysql

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!

Upvotes: 25

Views: 89920

Answers (2)

Mchl
Mchl

Reputation: 62395

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;

Upvotes: 8

Dan Grossman
Dan Grossman

Reputation: 52372

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

Upvotes: 69

Related Questions