Mysql SELECT all items in a particular month

Lightwalker used Ask the Experts™
Dear Experts,

This is so simple and yet I can not find out how to do it. I have a Date field in an order table and I would like to only select the orders from April of this year (2012). my Date field is in a timestamp format

How can I do that. All I have tried has not worked.

FROM order WHERE  Date = MONTH(04) 

Open in new window

Many thanks for your help
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012
select Date, DetailItemID
from order
where Date >= '2012-04-01' and Date < '2012-05-01';

You can actually do a "where year(Date)=2012 and month(Date)=4" but this method will not make use of any index on the Date column, which is critical when your data is of considerable size.  

I also did not use "where Date between '2012-04-01' and '2012-04-30' because this will not catch those that happened after '2012-04-30 00:00:00' or practically the whole April 30.  Maybe "where Date between '2012-04-01' and '2012-05-01'" can work for you if you are not worried about records that will happen at exactly midnight the start of the month (because '2012-05-01 00:00:00' will be counted on both "Date between '2012-04-01' and '2012-05-01'" and "Date between '2012-05-01' and '2012-06-01'"


Your a star, so simple, thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial