Selecting the no of rows from the database using the year stored in date field

Can any one help me get the total # of records from a database table, using year?

I have a table - in that I saved the exam date in a date field. I need to get the total # of records from that table using the year only.

I am using mysql.

This is my table

|  e_id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| exam_name   | varchar(50) | NO   |     | NULL    |                |
| exam_date   | date        | NO   |     | NULL    |                |

I nee开发者_运维问答d to to get the total no of exam held in a particular year only

You can use functions, like DATE_FORMAT() in @Brian's answer, or the YEAR() function like this:

SELECT COUNT(*) as total 
FROM table 
WHERE YEAR(date_field) = 2011

However, this approach will not be fast if the table has more than a few thousands rows because it will have to fully scan the whole table and apply the function for all the records of the table . It's better, in any case, to have a condition like @Ruslan's (if it's a DATETIME field) or like this if it's a DATE field:

WHERE date_field BETWEEN '2011-01-01' AND '2011-12-31'

I prefer the condition that works for either (DATE or DATETIME) types:

WHERE date_field >= '2011-01-01' 
  AND date_field <  '2012-01-01'

If you have an index on the date_field, the query will use the index for the counting without scanning the full table and will be many times faster.

I think this is what you want? Your question is a little vague though. SELECT COUNT(*) as 'total' FROM table WHERE DATE_FORMAT(date_field,'%Y') = '2011'

table is the name of your table, date_field is the field in your table where the date is stored, and then you can just replace 2011 with whatever year you are trying to search for.

In MYSQL, you can do something like:

BETWEEN '2010-05-31 00:00:00' AND '2010-06-06 23:59:59' 

And as you would like to query by year only, that would be:

BETWEEN '2005-01-01 00:00:00' AND '2011-11-31 23:59:59' 

Hope that helps ;)