MySQL MONTHNAME() function
MONTHNAME() function
MySQL MONTHNAME() returns the full name of the month for a given date. The return value is within the range of 1 to 12 ( January to December). It Returns NULL when month part for the date is 0 or more than 12. It provides a way to obtain the month's name for various date-related calculations and data presentation.
This function is useful in -
- MONTHNAME() is valuable for presenting date-based data in a human-readable format, enhancing data visualization.
- The function is valuable for aggregating and summarizing data by month name, providing insights into monthly trends.
- MONTHNAME() aids in analyzing and categorizing data based on the names of the months.
- The function is useful in international applications, as it returns month names based on the language settings.
- MONTHNAME() can be used to generate custom reports that include the full names of the months.
- MONTHNAME() helps in time series analysis, where you can create timelines with month names as labels.
- The function is used to create meaningful labels for events or activities that occurred in specific months.
Syntax:
MONTHNAME(date1)
Where date1 is a date.
Syntax Diagram:
MySQL Version: 8.0
Pictorial Presentation:
Example: MySQL MONTHNAME() function
The following statement will return the full name of the month for the given date 2009-05-18.
Code:
SELECT MONTHNAME('2009-05-18');
Output:
mysql> SELECT MONTHNAME('2009-05-18');
+-------------------------+
| MONTHNAME('2009-05-18') |
+-------------------------+
| May |
+-------------------------+
1 row in set (0.02 sec)
Example: MONTHNAME() function using table
The following statement will return invoice_no and the full name of the month for the invoice_dt from the table purchase.
Sample table: purchase
+------------+------------+----------------+------------+------------+---------+---------------------------------+----------+---------+-------------+-------------+------------+ | invoice_no | invoice_dt | ord_no | ord_date | receive_dt | book_id | book_name | pub_lang | cate_id | receive_qty | purch_price | total_cost | +------------+------------+----------------+------------+------------+---------+---------------------------------+----------+---------+-------------+-------------+------------+ | INV0001 | 2008-07-15 | ORD/08-09/0001 | 2008-07-06 | 2008-07-19 | BK001 | Introduction to Electrodynamics | English | CA001 | 15 | 75.00 | 1125.00 | | INV0002 | 2008-08-25 | ORD/08-09/0002 | 2008-08-09 | 2008-08-28 | BK004 | Transfer of Heat and Mass | English | CA002 | 8 | 55.00 | 440.00 | | INV0003 | 2008-09-20 | ORD/08-09/0003 | 2008-09-15 | 2008-09-23 | BK005 | Conceptual Physics | NULL | CA001 | 20 | 20.00 | 400.00 | | INV0004 | 2007-08-30 | ORD/07-08/0005 | 2007-08-22 | 2007-08-30 | BK004 | Transfer of Heat and Mass | English | CA002 | 15 | 35.00 | 525.00 | | INV0005 | 2007-07-28 | ORD/07-08/0004 | 2007-06-25 | 2007-07-30 | BK001 | Introduction to Electrodynamics | English | CA001 | 8 | 25.00 | 200.00 | | INV0006 | 2007-09-24 | ORD/07-08/0007 | 2007-09-20 | 2007-09-30 | BK003 | Guide to Networking | Hindi | CA003 | 20 | 45.00 | 900.00 | +------------+------------+----------------+------------+------------+---------+---------------------------------+----------+---------+-------------+-------------+------------+
Code:
SELECT invoice_no,MONTHNAME(invoice_dt)
FROM purchase;
Output:
mysql> SELECT invoice_no,MONTHNAME(invoice_dt)
-> FROM purchase;
+------------+-----------------------+
| invoice_no | MONTHNAME(invoice_dt) |
+------------+-----------------------+
| INV0001 | July |
| INV0002 | August |
| INV0003 | September |
| INV0004 | August |
| INV0005 | July |
| INV0006 | September |
+------------+-----------------------+
6 rows in set (0.05 sec)
Example: MONTHNAME() function with where clause
The following statement will return invoice_no and the full name of the month for the invoice_dt as MONTHNAME(invoice_dt) from the table purchase, making sure that value of the MONTHNAME(invoice_dt) is more than 7 (i.e. July).
Sample table: purchase
+------------+------------+----------------+------------+------------+---------+---------------------------------+----------+---------+-------------+-------------+------------+ | invoice_no | invoice_dt | ord_no | ord_date | receive_dt | book_id | book_name | pub_lang | cate_id | receive_qty | purch_price | total_cost | +------------+------------+----------------+------------+------------+---------+---------------------------------+----------+---------+-------------+-------------+------------+ | INV0001 | 2008-07-15 | ORD/08-09/0001 | 2008-07-06 | 2008-07-19 | BK001 | Introduction to Electrodynamics | English | CA001 | 15 | 75.00 | 1125.00 | | INV0002 | 2008-08-25 | ORD/08-09/0002 | 2008-08-09 | 2008-08-28 | BK004 | Transfer of Heat and Mass | English | CA002 | 8 | 55.00 | 440.00 | | INV0003 | 2008-09-20 | ORD/08-09/0003 | 2008-09-15 | 2008-09-23 | BK005 | Conceptual Physics | NULL | CA001 | 20 | 20.00 | 400.00 | | INV0004 | 2007-08-30 | ORD/07-08/0005 | 2007-08-22 | 2007-08-30 | BK004 | Transfer of Heat and Mass | English | CA002 | 15 | 35.00 | 525.00 | | INV0005 | 2007-07-28 | ORD/07-08/0004 | 2007-06-25 | 2007-07-30 | BK001 | Introduction to Electrodynamics | English | CA001 | 8 | 25.00 | 200.00 | | INV0006 | 2007-09-24 | ORD/07-08/0007 | 2007-09-20 | 2007-09-30 | BK003 | Guide to Networking | Hindi | CA003 | 20 | 45.00 | 900.00 | +------------+------------+----------------+------------+------------+---------+---------------------------------+----------+---------+-------------+-------------+------------+
Code:
SELECT invoice_no,MONTHNAME(invoice_dt)
FROM purchase
WHERE MONTH(invoice_dt)>7;
Output:
mysql> SELECT invoice_no,MONTHNAME(invoice_dt)
-> FROM purchase
-> WHERE MONTH(invoice_dt)>7;
+------------+-----------------------+
| invoice_no | MONTHNAME(invoice_dt) |
+------------+-----------------------+
| INV0002 | August |
| INV0003 | September |
| INV0004 | August |
| INV0006 | September |
+------------+-----------------------+
4 rows in set (0.00 sec)
Video Presentation:
All Date and Time Functions:
Click here to see the MySQL Date and time functions.