Select by Month of a field

Simply use MONTH() and YEAR():

SELECT * FROM Project WHERE MONTH(DueDate) = 1 AND YEAR(DueDate) = 2010

You could use a between statement:

select * from Project 
  where DueDate between '2010-01-01' and '2010-02-01'

Do not use this here recommended solution with MONTH() and YEAR(). It prevents MySQL to use index on column DueDate if you have one and it forces MySQL to examine all rows in table.

Instead, use BETWEEN clause like this:

SELECT * FROM Project 
WHERE DueDate BETWEEN '2010-01-01' AND '2010-02-01'

Or another solution with IN clause:

SELECT * FROM Project 
WHERE DueDate IN ('2010-01-01', '2010-01-02', '2010-01-03', ..., '2010-01-31')

This two solutions allows MySQL to use index, so performance will be better.

Tags:

Mysql