MySQL MONTH() function

MONTH() function

MySQL MONTH() returns the MONTH for the date within a range of 1 to 12 ( January to December). It Returns 0 when MONTH part for the date is 0.

This function is useful in -

• MONTH() aids in calculating time intervals, durations, and differences based on the month component.
• The function is used to track anniversaries or events that occur on specific months of the year.
• MONTH() is useful for filtering data based on specific months, allowing you to focus on subsets of data.
• MONTH() is valuable for aggregating data by month, allowing you to summarize data over time.
• In graphical or tabular representations, MONTH() can be used to label axes or data points with month values.
• MONTH() is valuable for analyzing date-based data, allowing you to focus on the month part of a date.
• The function is used to categorize data based on months, allowing you to perform seasonal analysis and reporting.

Syntax:

MONTH(date1)

Where date1 is a date.

Syntax Diagram:

MySQL Version: 8.0

Pictorial Presentation:

Example: MySQL MONTH() function

The following statement will return the MONTH for the given date 2009-05-18.

Code:

SELECT MONTH('2009-05-18');

Output:

mysql> SELECT MONTH('2009-05-18');
+---------------------+
| MONTH('2009-05-18') |
+---------------------+
|                   5 | 
+---------------------+
1 row in set (0.01 sec)

Example : MONTH() function with current date

The above statement will return the MONTH for the current date obtained by CURRENT_DATE().

Code:

SELECT MONTH(CURRENT_DATE());

Note: Since CURRENT_DATE() is used, your output may vary from the output shown.

Output:

mysql> SELECT MONTH(CURRENT_DATE());
+-----------------------+
| MONTH(CURRENT_DATE()) |
+-----------------------+
|                     4 | 
+-----------------------+
1 row in set (0.00 sec)

Example: MONTH() function using table

Sample table: publisher

The following statement will return the MONTH as ‘Estd MONTH’ for the ‘estd’ column as 'Estd.MONTH' (along with the name of the publisher and date of establishment) from the publisher table.

Code:

SELECT pub_name,estd,MONTH(estd) as 'Estd. MONTH'
FROM publisher;

Explanation:

The above statement will return the MONTH as ‘Estd MONTH’ for the ‘estd’ column as 'Estd.MONTH' (along with the name of the publisher and date of establishment) from the publisher table.

Output:

mysql> SELECT pub_name,estd,MONTH(estd) as 'Estd. MONTH'
    -> FROM publisher;
+------------------------------+------------+-------------+
| pub_name                     | estd       | Estd. MONTH |
+------------------------------+------------+-------------+
| Jex Max Publication          | 1969-12-25 |          12 | 
| BPP Publication              | 1985-10-01 |          10 | 
| New Harrold Publication      | 1975-09-05 |           9 | 
| Ultra Press Inc.             | 1948-07-10 |           7 | 
| Mountain Publication         | 1975-01-01 |           1 | 
| Summer Night Publication     | 1990-12-10 |          12 | 
| Pieterson Grp. of Publishers | 1950-07-15 |           7 | 
| Novel Publisher Ltd.         | 2000-01-01 |           1 | 
+------------------------------+------------+-------------+
8 rows in set (0.15 sec)

Example: MONTH() function with where clause

The following statement will return the name of the publisher, date of the establishment and MONTH of the establishment as 'Estd.MONTH', making sure that the value of the 'Estd.MONTH' is more than 7.

Sample table: publisher

Code:

SELECT pub_name,estd,MONTH(estd) as 'Estd. MONTH'              
FROM publisher        
WHERE MONTH(estd)>7;

Output:

mysql> SELECT pub_name,estd,MONTH(estd) as 'Estd. MONTH'              
    -> FROM publisher        
    -> WHERE MONTH(estd)>7;
+--------------------------+------------+-------------+
| pub_name                 | estd       | Estd. MONTH |
+--------------------------+------------+-------------+
| Jex Max Publication      | 1969-12-25 |          12 | 
| BPP Publication          | 1985-10-01 |          10 | 
| New Harrold Publication  | 1975-09-05 |           9 | 
| Summer Night Publication | 1990-12-10 |          12 | 
+--------------------------+------------+-------------+
4 rows in set (0.13 sec)

Video Presentation:

All Date and Time Functions :

Click here to see the MySQL Date and time functions.

Previous: MINUTE()
Next: MONTHNAME()