MySQL QUARTER() function
In this context, we will learn how we can use the MySQL QUARTER() function with proper syntax and good examples.
Introduction of MySQL QUARTER() function
The QUARTER() function in MySQL returns the Quarter of the year for a given date value. It returns a number from 1 to 4.
Syntax of the MySQL QUARTER() function
The syntax of the MySQL QUARTER() function is given as follows:
QUARTER(dt)
Parameters or arguments used in MySQL QUARTER() function:
There is only one parameter accepted by the QUARTER() function in MySQL, which is given as follows:
DT: The date or DateTime from which we want to extract the Quarter in MySQL.
Returns:
It will return 1 if the given date is in the range January-March, return 2 for April-June, return 3 for July-September, and if the date is in the range from October-December, it returns 4.
Application used for QUARTER() function:
The QUARTER() function can be used in the given below MySQL versions.:
- MySQL 5.7
- MySQL 5.6
- MySQL 5.5
- MySQL 5.1
- MySQL 5.0
- MySQL 4.1
- MySQL 4.0
- MySQL 3.23
Examples of MySQL QUARTER() function:
Now we will look into some MySQL QUARTER() function examples and will explore how we can use the QUARTER function in MySQL.
Example-1 :
In this example, we will find the Current QUARTER Using the QUARTER() Function in MySQL.
SELECT QUARTER(NOW()) AS CURRENT_QUARTER;
Output:
CURRENT_QUARTER
3
1 row in set (0.00 sec)
Example-2 :
In this example, we will find the Quarter from the given DateTime Using the QUARTER() Function.
SELECT QUARTER('2020-03-26 08:09:22') AS QUARTER_NUMBER;
Output:
QUARTER_NUMBER
1
1 row in set (0.00 sec)
Example-3 :
In this example, we will find the Quarter from the given DateTime Using the QUARTER() Function when the date is NULL.
SELECT QUARTER(NULL) AS QUARTER_NUMBER;
Output:
QUARTER_NUMBER
NULL
1 row in set (0.00 sec)
Example-4:
In this example we will create a table named Commodity which allows you to perform the quarter function on Selling_Date.
CREATE TABLE Commodity(
Commodity_id INT AUTO_INCREMENT,
Commodity_name VARCHAR(100) NOT NULL,
Purchasing_price DECIMAL(13, 2) NOT NULL,
Selling_price DECIMAL(13, 2) NOT NULL,
Selling_Date Date NOT NULL,
PRIMARY KEY(Commodity_id)
);
Now, we will insert some data to the Commodity table.
INSERT INTO
Commodity(Commodity_name, Purchasing_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, '2018-01-27' ),
('Mercedes-Benz C-Class', 4000000.00, 6000000.00, '2018-04-01'
),
('Jaguar F-PACE,' 5000000.00, 7000000.00, '2018-12-26' ),
('Porsche Macan', 6500000.00, 8000000.00, '2018-04-16' ) ;
So, Our table looks like this:
| Commodity_id | Commodity_name | Purchasing_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 |
We will find the number of Commodities sold per Quarter using the MONTH () function.
SELECT
QUARTER(Selling_Date) as quarter,
COUNT(Commodity_id) as COMMODITY_SOLD
FROM
Commodity
GROUP BY QUARTER(Selling_Date)
ORDER BY QUARTER(Selling_Date);
Output :
| QUARTER | COMMODITY_SOLD |
| 1 | 2 |
| 2 | 3 |
| 3 | 1 |
| 4 | 2 |
Application of MySQL QUARTER() function:
This function is used to return the Quarter of the year for a given date value.
Summary:
In the above context, we have learned how to use the QUARTER() function in MySQL to return the Quarter of the year for a given date value.