Retrieving month from date field in my database I have dates stored in database with the function : CURDATE() , when a user makes an order . With a form where the admin chooses the month and the year, I want a query to display all the orders made in that month and year . How can I do that ?
This works

SELECT MONTH(CURDATE());


With the final query like

SELECT    *
FROM      tbl_orders
WHERE     MONTH(date) = @month
AND       YEAR(date) = @year;


Here @month and @year are numeric values from the administrator. Defaults could be

SET @month = MONTH(CURDATE());
SET @year = YEAR(CURDATE);


This is the quick and dirty solution applicable for a small table. If performance is an issue, consider putting a binary tree index on the date column or breaking it down into (month, year) with a hash index.

Binary tree index allows comparison like so

WHERE     date &gt;= CONCAT(@year, '-', @month, '-', '01')
AND       date &lt;  CONCAT(@year, '-', @month+1, '-', '01');


But this does not work when @month=12 unless you do some complicated date math, which can be done elsewhere. Note that you can do concatenation elsewhere too, to enable query caching.

With hash index

SELECT    *
FROM      tbl_orders
WHERE     month = @month
AND       year  = @year;