How to get month using date in MySQL

Question

Possible Duplicate:
how do I get month from date in mysql

I want to get month using date example 2011-04-02 so I want month april. How to get this in MySQL?

Answer 1

SELECT MONTHNAME(date) AS monthName for January, February...

SELECT MONTH(date) AS monthName for 1, 2...

Answer 2

SELECT MONTHNAME(`date`) AS month_name FROM table_name;

You can use MONTHNAME() to get the month name. If you want month number, consider to use MONTH()

Answer 3

You can have a much more elegant solution to this if you use a second table as a date dimension table, and join your date field to it, in order to extract more useful information. This table can contain dates, month names, financial quarters, years, days of week, weekends, etc.

It is a really tiny table, only 365(ish) rows per year of data you have... And you can easily write some code to populate this table with as much data as you require. I did mine in Excel, exported as a CSV file and then imported the data into a blank table.

It also gives lots of benefits, for example, imagine a monthly data table with the following fields (and any others you can think of!) fully populated for all the months in a given range;

• Date (E.g. 2009-04-01)
• Day (E.g. 1)
• Day of Week (E.g. Wednesday)
• Month (E.g. 4)
• Year (E.g. 2009)
• Financial Year (E.g. 2009/10)
• Financial Quarter (E.g. 2009Q1)
• Calendar Quarter (E.g. 2009Q2)

Then combining this with your own table as follows;

SELECT `DT`.`monthName`
FROM `your_table`
INNER JOIN `dateTable` as DT
    ON `your_table`.`your_date_field`  =  `dateTable`.`theDate`

There are many other nice outputs that you can get from this data.

Hope that helps!