MySQL QUARTER() – How to find the quarter of the year in MySQL?

In this tutorial, we will learn about the MySQL QUARTER() functions. Let us first start by understanding by what a quarter means in terms of date and time.  A quarter is a three-month period on a company’s financial calendar that acts as a basis for periodic financial reports and the paying of dividends.

The quarter is usually one-fourth of a year.

• Quarter 1 is from January to March
• Quarter 2 is from April to June
• Quarter 3 is from July to September
• and Quarter 4 is from October to December

Suppose you are a manager and you want to know which employee joined in which quarter of the year. How would you do this? For this, MySQL provides us with the QUARTER() function. The MySQL QUARTER() function is used to return the quarter of the year for a given date value (a number from 1 to 4).

Syntax of MySQL QUARTER()

QUARTER(date) Code language: SQL (Structured Query Language) (sql)

Where ‘date’ is the date or datetime from which you want to extract the quarter value.

Examples of MySQL QUARTER()

Let us kick things off with a couple of basic examples. Let us find in which quarter do the following dates come – 2021-03-02 and 2020-08-15. We will use the SELECT statement, an alias called Quarter and the QUARTER() function. The query is –

SELECT QUARTER("2021-03-02") AS Quarter; 
SELECT QUARTER("2020-08-15") AS Quarter;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL QUARTER() With A Wrong Date

Let us see how QUARTER() behaves with a wrong date value. A wrong date value is one in which the month value is greater than 12 or the day value is greater than 31. Consider the below query.

SELECT QUARTER("2020-45-15") AS Quarter;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL QUARTER() With Numeric Value

We can also pass a date value in its numeric context to the QUARTER() function. In the numeric context, the date value is in the format YYYYMMDD. How about passing the following two date values in the numeric context to the QUARTER() function – 20200402 and 20201212? Consider the below query –

SELECT QUARTER(20200402) AS Quarter; 
SELECT QUARTER(20201212) AS Quarter;Code language: SQL (Structured Query Language) (sql)

Note that, we do not pass the date value in quotations if we are using the numeric context. The output is –

QUARTER() With CURDATE()

Let us use the QUARTER() function with the CURDATE() function. This will tell us the current quarter. We will write a query that displays the current date and the current quarter. The query is –

SELECT CURDATE() AS 'Current Date', QUARTER(CURDATE()) AS Quarter;Code language: SQL (Structured Query Language) (sql)

The QUARTER(CURDATE()) part in the above query is used to find the current quarter, First the current date value is returned by the CURDATE() function and then that value is passed to MySQL QUARTER() function which returns the quarter value. The output is – 

QUARTER() With Datetime Value

As I mentioned in the syntax, we can also pass a datetime value to the MySQL QUARTER() function. Let us find the quarter of the following datetime value – “2020-11-23 12:11:47”. The query is – 

SELECT QUARTER("2020-11-23 12:11:47") AS Quarter;Code language: SQL (Structured Query Language) (sql)

And the output is –

Working With Tables

Consider the below Employee table.

Let us display the employee id, employee name, their joining date and the quarter in which they joined the company using the QUARTER() function. The query for this is –

SELECT eid, Name, Date_Joined, QUARTER(Date_Joined) FROM Employee;Code language: SQL (Structured Query Language) (sql)

And the output is –

Conclusion

The MySQL QUARTER() function is widely used while retrieving the value of the quarter from a time/datetime value. I would encourage you to practice different examples of it. QUARTER() finds a lot of use cases in databases with financial data.

References

• MySQL Documentation on QUARTER() function.