How to Get the First Day of a Quarter in MySQL

Get the First Day of a Quarter in MySQL, Quarterly reporting is a common requirement in business analytics. Sales teams compare Q1 with Q2, finance departments track quarterly revenue, and analysts often need to group transactions into three-month periods.

But there is a practical challenge: given any date, how do you determine the first day of the quarter it belongs to?

For example:

• February 10, 2024 → January 1, 2024
• May 19, 2024 → April 1, 2024
• July 30, 2024 → July 1, 2024
• November 25, 2024 → October 1, 2024

MySQL doesn’t provide a simple FIRST_DAY_OF_QUARTER() function, but you can calculate it using YEAR(), QUARTER(), MAKEDATE(), and date intervals.

The following expression does the job:

MAKEDATE(YEAR(sales_date), 1)+INTERVAL QUARTER(sales_date) QUARTER-INTERVAL1 QUARTER

Let’s break down how it works and how you can use it in real SQL queries.

What Is a Quarter?

A calendar year is divided into four quarters:

So if you have a date such as:

2024-08-15

the date belongs to Q3, which begins on:

2024-07-01

The goal is to make MySQL calculate that starting date automatically.

Understanding QUARTER() in MySQL

The first piece of the calculation is the QUARTER() function.

For example:

SELECT QUARTER('2024-02-10');

returns:

1

Likewise:

SELECT QUARTER('2024-05-19');

returns:

2

And:

SELECT QUARTER('2024-08-15');

returns:

3

Finally:

SELECT QUARTER('2024-11-25');

returns:

4

So QUARTER() tells us which three-month period contains the date.

Building the First-Day Calculation

We can start by getting January 1 of the same year.

MAKEDATE(YEAR(sales_date), 1)

For a date in 2024:

YEAR(sales_date) = 2024

and:

MAKEDATE(2024, 1)

produces:

2024-01-01

Now we know the beginning of the year.

The next step is to move forward to the appropriate quarter.

The complete expression is:

MAKEDATE(YEAR(sales_date), 1)+INTERVAL QUARTER(sales_date) QUARTER-INTERVAL1 QUARTER

The QUARTER(sales_date) value tells MySQL how many quarters to move forward.

Subtracting one quarter afterward converts the calculation into the beginning of the current quarter.

Example Dataset

Let’s create a simple sales table:

CREATETABLE sales (  store_ID INTPRIMARYKEY,  item TEXT NOTNULL,  sales_date DATENOTNULL);

Insert some sample sales:

INSERTINTO sales VALUES (1, 'Oranges', '2024-02-10');INSERTINTO sales VALUES (2, 'Apples', '2024-11-25');INSERTINTO sales VALUES (3, 'Bananas', '2024-07-30');INSERTINTO sales VALUES (4, 'Melons', '2024-01-14');INSERTINTO sales VALUES (5, 'Grapes', '2024-05-19');

View the table:

SELECT*FROM sales;

The data looks like this:

+----------+---------+------------+| store_ID | item    | sales_date |+----------+---------+------------+|        1 | Oranges | 2024-02-10 ||        2 | Apples  | 2024-11-25 ||        3 | Bananas | 2024-07-30 ||        4 | Melons  | 2024-01-14 ||        5 | Grapes  | 2024-05-19 |+----------+---------+------------+

Get the First Day of the Quarter

Now apply the quarter calculation:

SELECT  sales_date,  MAKEDATE(YEAR(sales_date), 1)+INTERVAL QUARTER(sales_date) QUARTER-INTERVAL1 QUARTER AS first_dayFROM sales;

The result is:

+------------+------------+| sales_date | first_day  |+------------+------------+| 2024-02-10 | 2024-01-01 || 2024-11-25 | 2024-10-01 || 2024-07-30 | 2024-07-01 || 2024-01-14 | 2024-01-01 || 2024-05-19 | 2024-04-01 |+------------+------------+

Now each sales date has been mapped to the beginning of its quarter.

Why the Calculation Works

Consider this date:

2024-05-19

First:

YEAR('2024-05-19')

returns:

2024

Then:

MAKEDATE(2024, 1)

gives:

2024-01-01

Next:

QUARTER('2024-05-19')

returns:

2

So MySQL moves forward two quarters and then subtracts one quarter.

The final result is:

2024-04-01

which is exactly the first day of Q2.

A Simple Quarter Reference

You can think of the calculation as:

Q1 → January 1Q2 → April 1Q3 → July 1Q4 → October 1

For example:

2024-02-10 → Q1 → 2024-01-012024-05-19 → Q2 → 2024-04-012024-07-30 → Q3 → 2024-07-012024-11-25 → Q4 → 2024-10-01

Why This Is Useful in Data Analysis

Finding the first day of a quarter is more than a date-formatting trick.

It can be extremely useful when building analytical queries.

For example, suppose a sales table contains thousands or millions of transactions. You may want to group those transactions by quarter.

A calculated quarter-start date gives every transaction a common reference point.

For example:

2024-01-05 → 2024-01-012024-02-18 → 2024-01-012024-03-27 → 2024-01-012024-04-03 → 2024-04-012024-05-16 → 2024-04-012024-06-29 → 2024-04-01

This makes quarterly aggregation much easier.

Group Sales by Quarter

You can use the calculated date in a GROUP BY query.

For example:

SELECT  MAKEDATE(YEAR(sales_date), 1)+INTERVAL QUARTER(sales_date) QUARTER-INTERVAL1 QUARTER AS quarter_start,COUNT(*) AS sales_countFROM salesGROUPBY quarter_startORDERBY quarter_start;

Now all sales belonging to the same quarter can be grouped together.

This approach can be useful for:

• Quarterly sales reports
• Revenue analysis
• Financial dashboards
• Customer acquisition reports
• Product performance
• Inventory analysis
• Business forecasting

Finding the Current Quarter’s Start Date

You can also apply the same concept to the current date.

For example:

SELECT  MAKEDATE(YEAR(CURDATE()), 1)+INTERVAL QUARTER(CURDATE()) QUARTER-INTERVAL1 QUARTER AS current_quarter_start;

This dynamically calculates the beginning of the quarter containing today’s date.

That can be useful in dashboards and automated reporting systems where the query runs repeatedly.

First Day of the Previous Quarter

Once you have the beginning of the current quarter, you can move backward by one quarter:

SELECT  MAKEDATE(YEAR(CURDATE()), 1)+INTERVAL QUARTER(CURDATE()) QUARTER-INTERVAL2 QUARTER AS previous_quarter_start;

This can be useful when comparing:

Current Quarter vs Previous Quarter

or calculating quarter-over-quarter growth.

First Day of the Next Quarter

Similarly, you can calculate the beginning of the next quarter:

SELECT  MAKEDATE(YEAR(CURDATE()), 1)+INTERVAL QUARTER(CURDATE()) QUARTER AS next_quarter_start;

This is particularly useful for forecasting and planning queries.

Be Careful With Fiscal Quarters

The examples above assume calendar quarters:

• Q1 = January–March
• Q2 = April–June
• Q3 = July–September
• Q4 = October–December

However, some organizations use fiscal years that start in a different month.

For example, a business might define its fiscal year as starting in April.

In that situation, January doesn’t necessarily belong to the organization’s fiscal Q1.

You would need a different calculation based on the organization’s fiscal calendar.

This distinction is important when working with financial or enterprise datasets.

An Alternative: CASE Expression

For readability, you can also calculate quarter-start dates using a CASE expression.

For example:

SELECT  sales_date,CASEWHENMONTH(sales_date) BETWEEN1AND3THEN MAKEDATE(YEAR(sales_date), 1)WHENMONTH(sales_date) BETWEEN4AND6THEN MAKEDATE(YEAR(sales_date), 1) +INTERVAL3MONTHWHENMONTH(sales_date) BETWEEN7AND9THEN MAKEDATE(YEAR(sales_date), 1) +INTERVAL6MONTHELSE      MAKEDATE(YEAR(sales_date), 1) +INTERVAL9MONTHENDAS first_dayFROM sales;

This is more verbose, but it makes the quarter boundaries very explicit.

The QUARTER() approach is generally more compact.

Quick Reference

The main expression is:

MAKEDATE(YEAR(sales_date), 1)+INTERVAL QUARTER(sales_date) QUARTER-INTERVAL1 QUARTER

Use it with an alias:

SELECT  sales_date,  MAKEDATE(YEAR(sales_date), 1)+INTERVAL QUARTER(sales_date) QUARTER-INTERVAL1 QUARTER AS first_dayFROM sales;

The results follow this pattern:

Final Takeaway

MySQL doesn’t have a dedicated FIRST_DAY_OF_QUARTER() function, but you can derive the value using a combination of YEAR(), QUARTER(), MAKEDATE(), and interval arithmetic.

The key expression is:

MAKEDATE(YEAR(sales_date), 1)+INTERVAL QUARTER(sales_date) QUARTER-INTERVAL1 QUARTER

It converts any calendar date into the first day of the quarter containing that date.

Once you have a standardized quarter-start date, it becomes much easier to group transactions, compare quarterly performance, build financial reports, and create time-based analytics in MySQL.