How to Get the First Day of the Month in MySQL

Working with dates is a common requirement in MySQL, especially when building sales reports, financial dashboards, business intelligence applications, and analytics queries. One frequently needed operation is converting any date into the first day of its corresponding month.

For example, if you have a date such as 2024-02-18, you may want to return 2024-02-01. Similarly, 2024-11-25 should become 2024-11-01.

MySQL provides several ways to accomplish this. One simple approach is to use DATE_ADD() together with the DAY() function:

SELECT sales_date,
       DATE_ADD(sales_date, INTERVAL -DAY(sales_date)+1 DAY) AS first_day
FROM sales;

This technique subtracts the day number minus one from the original date, leaving the date on the first day of the same month.

Example: Get the First Day of the Month in MySQL

Suppose we have a table called sales containing information about products sold at different grocery stores.

We can create the table using the following SQL statement:

CREATE TABLE sales (
    store_ID INT PRIMARY KEY,
    item TEXT NOT NULL,
    sales_date DATE NOT NULL
);

Next, insert several sample records:

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

You can view the contents of the table with:

SELECT *
FROM sales;

The result is:

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

Get the First Day Using DATE_ADD()

To calculate the first day of the month for every value in sales_date, use:

SELECT sales_date,
       DATE_ADD(sales_date, INTERVAL -DAY(sales_date)+1 DAY) AS first_day
FROM sales;

The query returns:

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

The AS first_day portion gives the calculated column a descriptive name.

How the DATE_ADD() Formula Works

The important part of the query is:

DATE_ADD(sales_date, INTERVAL -DAY(sales_date)+1 DAY)

The DAY() function extracts the day-of-month from a date.

For example:

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

returns:

10

For 2024-02-10, the calculation becomes:

-DAY(sales_date) + 1
= -10 + 1
= -9

Therefore, MySQL subtracts nine days from 2024-02-10:

2024-02-10 - 9 days = 2024-02-01

For 2024-11-25, the calculation is:

-25 + 1 = -24

So:

2024-11-25 - 24 days = 2024-11-01

This works regardless of whether the month contains 28, 29, 30, or 31 days.

A Simpler MySQL Approach Using DATE_FORMAT()

Another convenient way to get the first day of the month is to use DATE_FORMAT():

SELECT sales_date,
       DATE_FORMAT(sales_date, '%Y-%m-01') AS first_day
FROM sales;

The result is:

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

This approach is particularly easy to understand because %Y-%m-01 explicitly constructs a date containing the original year, original month, and day 01.

Using LAST_DAY() to Find the Month Boundaries

MySQL also provides the LAST_DAY() function, which returns the last day of the month.

For example:

SELECT sales_date,
       LAST_DAY(sales_date) AS last_day
FROM sales;

To calculate the first day from the last day, you could use:

SELECT sales_date,
       DATE_SUB(LAST_DAY(sales_date), INTERVAL DAY(LAST_DAY(sales_date)) - 1 DAY) AS first_day
FROM sales;

Although this works, it is more complicated than the DATE_ADD() or DATE_FORMAT() approaches, so it is generally unnecessary when your only requirement is the first day of the month.

Get the First Day of the Current Month

If you want the first day of the current month rather than the month associated with a column, you can use:

SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01') AS first_day;

For example, if today’s date is:

2026-08-17

the query returns:

2026-08-01

You can also use:

SELECT DATE_ADD(CURDATE(), INTERVAL -DAY(CURDATE())+1 DAY) AS first_day;

Both approaches return the first day of the current month.

Get the First Day of the Previous Month

The same technique can be useful for reporting periods.

For example, to find the first day of the previous month:

SELECT DATE_FORMAT(
           DATE_SUB(CURDATE(), INTERVAL 1 MONTH),
           '%Y-%m-01'
       ) AS first_day_previous_month;

This is useful when creating monthly reports that automatically calculate the previous reporting period.

Get the First Day of the Next Month

You can similarly calculate the first day of the next month:

SELECT DATE_FORMAT(
           DATE_ADD(CURDATE(), INTERVAL 1 MONTH),
           '%Y-%m-01'
       ) AS first_day_next_month;

This can be useful for defining date ranges in recurring billing, subscription analytics, financial reporting, and data warehouse processes.

Get Monthly Sales Using the First Day of the Month

Calculating the first day of a month becomes especially useful when aggregating data.

Suppose your sales table also contains a sales_amount column:

CREATE TABLE monthly_sales (
    sale_id INT PRIMARY KEY,
    sales_date DATE NOT NULL,
    sales_amount DECIMAL(10,2) NOT NULL
);

You can group transactions by month using:

SELECT
    DATE_FORMAT(sales_date, '%Y-%m-01') AS month_start,
    SUM(sales_amount) AS total_sales
FROM monthly_sales
GROUP BY DATE_FORMAT(sales_date, '%Y-%m-01')
ORDER BY month_start;

This transforms individual transaction dates into monthly reporting periods.

For example:

+------------+-------------+
| month_start| total_sales |
+------------+-------------+
| 2024-01-01 |    15250.00 |
| 2024-02-01 |    18450.00 |
| 2024-03-01 |    21320.00 |
+------------+-------------+

This pattern is widely used in business intelligence and analytics applications.

Get the First Day of the Month for a DATETIME Column

The same concept can be applied when the column contains both a date and a time.

Suppose you have:

2024-11-25 14:35:20

You can use:

SELECT
    order_datetime,
    DATE_FORMAT(order_datetime, '%Y-%m-01') AS first_day
FROM orders;

The result will be:

+---------------------+------------+
| order_datetime      | first_day  |
+---------------------+------------+
| 2024-11-25 14:35:20 | 2024-11-01 |
+---------------------+------------+

If you need the result as a DATE value rather than a formatted string, you can use:

SELECT
    order_datetime,
    CAST(DATE_FORMAT(order_datetime, '%Y-%m-01') AS DATE) AS first_day
FROM orders;

Get the First Day and Last Day of the Month

For reporting applications, it is often useful to calculate both boundaries.

You can use:

SELECT
    sales_date,
    DATE_FORMAT(sales_date, '%Y-%m-01') AS first_day,
    LAST_DAY(sales_date) AS last_day
FROM sales;

The output will look like:

+------------+------------+------------+
| sales_date | first_day  | last_day   |
+------------+------------+------------+
| 2024-02-10 | 2024-02-01 | 2024-02-29 |
| 2024-11-25 | 2024-11-01 | 2024-11-30 |
| 2024-06-30 | 2024-06-01 | 2024-06-30 |
| 2024-01-14 | 2024-01-01 | 2024-01-31 |
| 2024-05-19 | 2024-05-01 | 2024-05-31 |
+------------+------------+------------+

This is particularly helpful when creating monthly date filters.

Which MySQL Method Should You Use?

There are several valid approaches, but they have slightly different advantages.

For most applications, DATE_FORMAT() is the easiest to read:

DATE_FORMAT(sales_date, '%Y-%m-01')

If you specifically want to demonstrate date arithmetic, the following is also an excellent option:

DATE_ADD(sales_date, INTERVAL -DAY(sales_date)+1 DAY)

Common Business Use Cases

Finding the first day of a month is a small SQL operation, but it has many practical applications.

Monthly Sales Reporting

Businesses can group sales transactions into monthly reporting periods:

SELECT
    DATE_FORMAT(sales_date, '%Y-%m-01') AS month_start,
    SUM(sales_amount) AS revenue
FROM sales
GROUP BY DATE_FORMAT(sales_date, '%Y-%m-01');

Customer Analytics

Organizations can use month-start dates to calculate customer acquisition by month:

SELECT
    DATE_FORMAT(signup_date, '%Y-%m-01') AS signup_month,
    COUNT(*) AS new_customers
FROM customers
GROUP BY DATE_FORMAT(signup_date, '%Y-%m-01');

Subscription Analytics

SaaS companies can use month boundaries for tracking subscriptions, churn, renewals, and monthly recurring revenue.

For example:

SELECT
    DATE_FORMAT(subscription_date, '%Y-%m-01') AS subscription_month,
    COUNT(*) AS subscriptions
FROM subscriptions
GROUP BY DATE_FORMAT(subscription_date, '%Y-%m-01');

Financial Reporting

Financial systems frequently need consistent monthly periods for revenue, expenses, budgets, and forecasts.

A month-start column makes it easier to join transactional data with monthly targets and reporting tables.

Business Intelligence Dashboards

Tools such as Power BI, Tableau, Looker, and other BI platforms often work with monthly aggregation fields. Creating a standardized month-start date in SQL can simplify downstream reporting and visualization.

Using a Month Start in a WHERE Clause

Suppose you want to retrieve all transactions from a particular month. A common approach is to define the start of the month and the start of the next month.

For example:

SELECT *
FROM sales
WHERE sales_date >= '2024-02-01'
  AND sales_date < '2024-03-01';

This approach is generally preferable to applying a function to the date column in the WHERE clause because it can make better use of an index on sales_date.

For a dynamic current-month query, you can use:

SELECT *
FROM sales
WHERE sales_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
  AND sales_date < DATE_FORMAT(
      DATE_ADD(CURDATE(), INTERVAL 1 MONTH),
      '%Y-%m-01'
  );

This returns records from the current month without requiring you to manually specify the dates.

Important Tip: Be Careful With DATE_FORMAT()

DATE_FORMAT() is excellent for displaying and grouping dates, but remember that it produces a formatted value.

For example:

SELECT DATE_FORMAT('2024-02-10', '%Y-%m-01');

returns:

2024-02-01

When building more complex queries, particularly queries involving date comparisons, consider whether you need a formatted date or a true date value.

For date arithmetic, functions such as DATE_ADD(), DATE_SUB(), and LAST_DAY() can sometimes be more appropriate.

Complete Example

Here is the complete example in one executable MySQL script:

CREATE TABLE sales (
    store_ID INT PRIMARY KEY,
    item TEXT NOT NULL,
    sales_date DATE NOT NULL
);

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

SELECT
    sales_date,
    DATE_ADD(
        sales_date,
        INTERVAL -DAY(sales_date) + 1 DAY
    ) AS first_day
FROM sales;

Expected result:

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

Frequently Asked Questions

How do I get the first day of the month in MySQL?

You can use:

SELECT DATE_ADD(date_column, INTERVAL -DAY(date_column)+1 DAY);

Another simple option is:

SELECT DATE_FORMAT(date_column, '%Y-%m-01');

How do I get the first day of the current month in MySQL?

Use:

SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01');

How do I get the last day of the month in MySQL?

Use the LAST_DAY() function:

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

The result is:

2024-02-29

Can I use this with a DATETIME column?

Yes. MySQL date functions can be used with DATETIME values. For example:

SELECT DATE_FORMAT(order_datetime, '%Y-%m-01')
FROM orders;

What is the easiest way to get month start in MySQL?

For readability, this is one of the simplest approaches:

DATE_FORMAT(sales_date, '%Y-%m-01')

For date arithmetic, you can use:

DATE_ADD(sales_date, INTERVAL -DAY(sales_date)+1 DAY)

Conclusion

Getting the first day of a month in MySQL is useful for monthly sales reports, financial analysis, customer analytics, subscription reporting, and business intelligence dashboards.

The DATE_ADD() approach:

SELECT sales_date,
       DATE_ADD(sales_date, INTERVAL -DAY(sales_date)+1 DAY) AS first_day
FROM sales;

works by subtracting the current day-of-month minus one from each date.

For a shorter and highly readable solution, you can also use:

SELECT sales_date,
       DATE_FORMAT(sales_date, '%Y-%m-01') AS first_day
FROM sales;

Understanding these date functions gives you a practical foundation for building more advanced MySQL queries involving monthly reporting periods, date ranges, financial analysis, and time-based aggregations.