how do I get month from date in mysql

mysql, php, phrase, sql

Solution

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

Problem

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?

Original source