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

Answer 2

Use the month() function.

select month(now());