MONTH() function in MySQL
MONTH() function in MySQL is used to find a month from the given date. It returns 0 when the month part for the date is 0 otherwise it returns month value between 1 and 12.
Syntax :
MONTH(date)
Parameter :
This function accepts one parameter
- date : The date or DateTime from which we want to extract the month.
Returns : It returns the value range from 1 to 12.
Example-1 :
Finding the Current Month Using MONTH() Function.
SELECT MONTH(NOW()) AS Current_Month;
Output :
CURRENT_MONTH |
---|
11 |
Example-2 :
Finding the Month from given DateTime Using Month() Function.
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.
SELECT MONTH(NULL) AS Month ;
Output :
Example-4 :
The MONTH function can also be used to find the total product sold for every month. To demonstrate create a table named.
Product :
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) );
Now insert some data to the Product table :
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 :
MONTH |
---|
NULL |
Product_id | Product_name | Buying_price | Selling_price | Selling_Date |
---|---|---|---|---|
1 | Audi Q8 | 10000000.00 | 15000000.00 | 2018-01-26 |
2 | Volvo XC40 | 2000000.00 | 3000000.00 | 2018-04-20 |
3 | Audi A6 | 4000000.00 | 5000000.00 | 2018-07-25 |
4 | BMW X5 | 5000500.00 | 7006500.00 | 2018-10-18 |
5 | Jaguar XF | 5000000.00 | 7507000.00 | 2019-01-27 |
6 | Mercedes-Benz C-Class | 4000000.00 | 6000000.00 | 2019-04-01 |
7 | Jaguar F-PACE | 5000000.00 | 7000000.00 | 2019-12-26 |
8 | Porsche 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 :
MONTH | PRODUCT_SOLD |
---|---|
1 | 2 |
4 | 3 |
7 | 1 |
10 | 1 |
12 | 1 |