how do I get month from date in mysql


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?



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:


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:



Mysql Tutorials

Mysql Howto..

mySQL: how to fill this questions - answers table

How to access the another system mysql database through java program?

How can I get updated row from MySQL in wordpress site?

How to select all subjects that are taken by all students such that all students took grade > 50 in them

How to create mysql trigger that inserts row into history table? [closed]

How to insert new values in MySql tables only those differents from rows that already exists?

PHP Mysql Check the row and how many have the same email

How to store a vector of time/value data in a database

How to insert XML content into mysql database using PHP

MySQL: How to select a column by number?

How to find most popular word occurrences in MySQL?

How to implement history of mysql events?

How to optimize mysql queries and decrease server load?

How to add dots and commas to number in MySQL query

How to do this query in Mysql?

How to set up MySQL in IBM Worklight

how to such a mysql join

How to merge results of two mysql tables in one output

Mysql: how to reuse a calculation within a query

How do I configure our MySQL ReplicationDriver for our JBoss 7 data source?

How to design similar structure tables in Mysql

How to check a checkbox querying MySQL which record is boolean

How to use mysql dump in php

How to sort varchar id on basis integer in it using mysql

mysql_query to only show posts by the user

How to check if any object is related to a row in a table with constrain of foreign key

How can I Insert many rows into a MySQL table and return the new IDs?

How can I delete a huge number of rows without freeze the UI?

How to insert image to mysql database in netbeans java

How to join across 3 tables in MySQL?