How to use the MySQL QUARTER() function

In this article, we will learn how to use the MySQL QUARTER() function, which returns the quarter of the year for a given date or datetime value. We will also see some examples of how to use this function in different situations, and explore some related functions that can be helpful for working with dates and quarters.

Syntax

The syntax of the QUARTER() function is as follows:

QUARTER(date)

The date parameter can be any valid date or datetime expression, or a string that can be converted to a date or datetime value. If the date parameter is invalid or NULL, the function returns NULL. The QUARTER() function returns an integer value from 1 to 4, representing the quarter of the year for the given date. For example, QUARTER('2023-01-15') returns 1, and QUARTER('2023-12-31') returns 4.

Examples

Let’s see some examples of how to use the QUARTER() function in MySQL.

Example 1: Get the quarter of the year for a date

We can use the QUARTER() function to get the quarter of the year for a date. For example:

SELECT QUARTER('2023-05-12') AS quarter;

This query will return the quarter of the year for the given date. In this case, the query will return 2, since May belongs to the second quarter of the year.

Example 2: Get the quarter of the year for the current date

We can use the QUARTER() function with the CURDATE() function, which returns the current date, to get the quarter of the year for the current date. For example:

SELECT QUARTER(CURDATE()) AS current_quarter;

This query will return the quarter of the year for the current date. For example, if the current date is 2023-12-15, the query will return 4, since December belongs to the fourth quarter of the year.

Example 3: Get the number of records for each quarter

We can use the QUARTER() function with the GROUP BY clause to get the number of records for each quarter in a table. For example, suppose we have a table called sales that stores the sales details of a company, with the following columns:

• sale_id: the unique identifier of the sale
• product_id: the unique identifier of the product sold
• sale_date: the date when the sale was made
• sale_amount: the amount of the sale

We can use the following query to get the number of sales and the total amount of sales for each quarter in the year 2023:

SELECT QUARTER(sale_date) AS quarter, COUNT(*) AS sale_count, SUM(sale_amount) AS sale_total
FROM sales
WHERE YEAR(sale_date) = 2023
GROUP BY quarter
ORDER BY quarter;

This query will return the quarter, the sale count, and the sale total for each quarter in the year 2023, ordered by the quarter. For example, the query might return something like this:

Related Functions

There are some other functions that are related to the QUARTER() function, and can be useful for working with dates and quarters. Here are some of them:

• YEAR(): This function returns the year of a date or datetime value. For example, YEAR('2023-01-15') returns 2023.
• MONTH(): This function returns the month of a date or datetime value. For example, MONTH('2023-01-15') returns 1.
• DAY(): This function returns the day of the month for a date or datetime value. For example, DAY('2023-01-15') returns 15.
• CURDATE(): This function returns the current date as a date value. For example, CURDATE() returns ‘2023-12-15’ if the current date is 2023-12-15.
• DATE_FORMAT(): This function returns the date or datetime value formatted according to a specified format. For example, DATE_FORMAT('2023-01-15', '%Y-%m-%d') returns ‘2023-01-15’.

Conclusion

In this article, we learned how to use the MySQL QUARTER() function, which returns the quarter of the year for a given date or datetime value. We also saw some examples of how to use this function in different situations, and explored some related functions that can be helpful for working with dates and quarters.