php - how do I get month from date in mysql

Question

I want to be able to fetch results from mysql with a statement like this:

SELECT * 
  FROM table 
 WHERE amount > 1000 

But I want to fetch the result constrained to a certain a month and year (based on input from user)... I was trying like this:

SELECT * 
  FROM table 
 WHERE amount > 1000 
   AND dateStart = MONTH('$m')   

...$m being a month but it gave error.

In that table, it actually have two dates: startDate and endDate but I am focusing on startDate. The input values would be month and year. How do I phrase the SQL statement that gets the results based on that month of that year?

Answer 1

You were close - got the comparison backwards (assuming startDate is a DATETIME or TIMESTAMP data type):

SELECT * 
  FROM table 
 WHERE amount > 1000 
   AND MONTH(dateStart) = {$m}

Caveats:

---

• Mind that you are using mysql_escape_string or you risk SQL injection attacks.
• Function calls on columns means that an index, if one exists, can not be used

Alternatives:

---

Because using functions on columns can't use indexes, a better approach would be to use BETWEEN and the STR_TO_DATE functions:

WHERE startdate BETWEEN STR_TO_DATE([start_date], [format]) 
                    AND STR_TO_DATE([end_date], [format])

See the documentation for formatting syntax.

Reference:

---

• MONTH
• YEAR
• BETWEEN
• STR_TO_DATE