Php – SQL query where() date’s year is $year

phpsql

I store dates in my database in a column of date data type.

Let's say I have column date where I store data like this "2011-01-01", "2012-01-01", "2012-02-02" etc.

Now I need to make SQL that selects only rows where date is equal to 2012

SELECT * FROM table WHERE date=hasSomehowYearEqualTo=2012

What would be the query like?

Best Solution

Do NOT use YEAR(date) - this will calculate YEAR(date) for all dates, even for those, you never use. It will also make use of an index impossible - worst case on the DB layer.

Use

$sql="SELECT * FROM table WHERE `date` BETWEEN '$year-01-01' AND '$year-12-31'"

As a general rule: If you have the choice between a calculation on a constant and a calculation on a field, use the former.