Open In App

MONTH() function in MySQL

Last Updated : 25 Jun, 2024
Comments
Improve
Suggest changes
Like Article
Like
Report

MySQL MONTH() function returns the month from the given date. It returns a month value between 1 and 12 or returns 0 when the month part of the date is 0. It’s a useful SQL function for date manipulation and analysis, particularly when dealing with reports or queries that require month-based grouping or filtering.

Syntax

MONTH(date)

Parameter:

MySQL MONTH() function accepts one parameter

  • date: The date or DateTime from which we want to extract the month.

Return Value:

  • An integer between 1 and 12 representing the month.

MySQL MONTH() Function Examples

Let’s look at some examples of the MONTH() function in MySQL.

Example 1: Find the current month using the MONTH() Function.

Query:

SELECT MONTH(NOW()) AS Current_Month;

Output:

CURRENT_MONTH
11

Example 2: Find the month from the given DateTime using Month() function.

Query:

SELECT MONTH('2015-09-26 08:09:22') AS MONTH;

Output:

MONTH
9

Example 3: Finding the month from given DateTime using MONTH() function when the date is NULL.

Query:

SELECT MONTH(NULL) AS Month ;

Output:

MONTH
NULL

Example 4: The MONTH function can also be used to find the total product sold for every month.

To demonstrate this, create a table named Product.

MySQL
CREATE TABLE Product(
   Product_id INT AUTO_INCREMENT,
   Product_name VARCHAR(100) NOT NULL,
   Buying_price DECIMAL(13, 2) NOT NULL,
   Selling_price DECIMAL(13, 2) NOT NULL,
   Selling_Date DATE NOT NULL,
   PRIMARY KEY(Product_id)
);

INSERT INTO Product(Product_name, Buying_price, Selling_price, Selling_Date)
VALUES
   ('Audi Q8', 10000000.00, 15000000.00, '2018-01-26'),
   ('Volvo XC40', 2000000.00, 3000000.00, '2018-04-20'),
   ('Audi A6', 4000000.00, 5000000.00, '2018-07-25'),
   ('BMW X5', 5000500.00, 7006500.00, '2018-10-18'),
   ('Jaguar XF', 5000000, 7507000.00, '2019-01-27'),
   ('Mercedes-Benz C-Class', 4000000.00, 6000000.00, '2019-04-01'),
   ('Jaguar F-PACE', 5000000.00, 7000000.00, '2019-12-26'),
   ('Porsche Macan', 6500000.00, 8000000.00, '2020-04-16');

So, Our table looks like:

Table Product:

Product_idProduct_nameBuying_price Selling_price Selling_Date 
1Audi Q8 10000000.00 15000000.00 2018-01-26
2Volvo XC402000000.00 3000000.00 2018-04-20
3 Audi A6 4000000.00 5000000.00 2018-07-25
4BMW X55000500.00 7006500.00 2018-10-18 
5Jaguar XF 5000000.00 7507000.002019-01-27
6Mercedes-Benz C-Class4000000.00 6000000.00 2019-04-01
7Jaguar F-PACE 5000000.00 7000000.002019-12-26
8Porsche Macan 6500000.00 8000000.00 2020-04-16

Now, we are going to find the number of products sold per month by using the MONTH() function.

SELECT  
   MONTH (Selling_Date) month,  
   COUNT(Product_id) Product_Sold
FROM Product
GROUP BY MONTH (Selling_Date)
ORDER BY MONTH (Selling_Date);

Output:

MONTHPRODUCT_SOLD
12
43
71
101
121

Important Points About MySQL MONTH() Function

  • The MONTH() function is used to extract the month portion of a date or datetime expression.
  • It returns an integer value representing the month from 1 to 12, where 1 corresponds to January and 12 to December.
  • This function can be applied to columns in a table or to date literals.
  • MONTH() is useful in queries where you need to filter, group, or sort data by month.
  • It is different from the MONTHNAME() function, which returns the full name of the month instead of an integer.
  • The MONTH() function helps in date manipulation and analysis by allowing you to easily isolate and work with the month component of dates.


Next Article
Article Tags :

Similar Reads