How to Add Months to a Date in MySQL

by

Add Months to a Date in MySQL, Working with dates is a routine part of data analysis. Whether you’re calculating a subscription renewal date, projecting a sales period, or determining a future reporting date, you’ll often need to move a date forward by a specific number of months.

MySQL makes this straightforward with the DATE_ADD() function.

For example, if you want to add three months to every date in a column, you can use:

SELECT sales_date,
       DATE_ADD(sales_date, INTERVAL 3 MONTH) AS add_three
FROM sales;

The original date remains unchanged, while MySQL calculates a new date based on the specified interval.

MySQL DATE_ADD() Syntax

The general syntax is:

DATE_ADD(date, INTERVAL value unit)

For adding months:

DATE_ADD(date, INTERVAL number MONTH)

For example:

SELECT DATE_ADD('2024-02-10', INTERVAL 3 MONTH);

The result is:

2024-05-10

The three important pieces are:

  • Date – the starting date
  • Number – how many months to add
  • MONTH – the unit being added

You can change the number depending on your requirement:

INTERVAL 1 MONTH
INTERVAL 3 MONTH
INTERVAL 6 MONTH
INTERVAL 12 MONTH

Example: Sales Dates

Let’s use a simple sales dataset.

Suppose a grocery company records the date of each sale in a table called sales.

Create the table:

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

Now insert some 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-07-30');
INSERT INTO sales VALUES (4, 'Melons', '2024-01-14');
INSERT INTO sales VALUES (5, 'Grapes', '2024-05-19');

View the data:

SELECT * FROM sales;

The table contains:

+----------+---------+------------+
| 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 |
+----------+---------+------------+

Now imagine that the business wants to know what the date will be three months after each sale.

Adding 3 Months to Every Date

We can calculate the new dates with:

SELECT sales_date,
       DATE_ADD(sales_date, INTERVAL 3 MONTH)
FROM sales;

The result is:

+------------+----------------------------------------+
| sales_date | DATE_ADD(sales_date, INTERVAL 3 MONTH) |
+------------+----------------------------------------+
| 2024-02-10 | 2024-05-10                             |
| 2024-11-25 | 2025-02-25                             |
| 2024-07-30 | 2024-10-30                             |
| 2024-01-14 | 2024-04-14                             |
| 2024-05-19 | 2024-08-19                             |
+------------+----------------------------------------+

Notice something important: the original sales_date values haven’t been changed.

DATE_ADD() simply calculates a new date in the query result.

That’s useful when you want to perform date calculations without modifying the underlying table.

Give the Calculated Column a Better Name

The automatically generated column name can be difficult to read.

Instead, use AS to create a meaningful alias:

SELECT sales_date,
       DATE_ADD(sales_date, INTERVAL 3 MONTH) AS add_three
FROM sales;

Now the output is much cleaner:

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

For production queries, descriptive aliases such as three_months_later are even easier to understand:

SELECT sales_date,
       DATE_ADD(sales_date, INTERVAL 3 MONTH) AS three_months_later
FROM sales;

Adding Different Numbers of Months

You aren’t limited to three months.

For example, add one month:

SELECT DATE_ADD(sales_date, INTERVAL 1 MONTH)
FROM sales;

Add six months:

SELECT DATE_ADD(sales_date, INTERVAL 6 MONTH)
FROM sales;

Add twelve months:

SELECT DATE_ADD(sales_date, INTERVAL 12 MONTH)
FROM sales;

You can even add a value stored in another column.

For example, if a table contains a months_to_add column:

SELECT sales_date,
       DATE_ADD(sales_date, INTERVAL months_to_add MONTH) AS future_date
FROM sales;

This allows each row to have its own interval.

Adding Months Across Years

One useful feature of DATE_ADD() is that MySQL automatically handles the transition into a new year.

For example:

SELECT DATE_ADD('2024-11-25', INTERVAL 3 MONTH);

returns:

2025-02-25

You don’t need to manually calculate the year change.

MySQL handles the calendar arithmetic for you.

An Important Edge Case: End-of-Month Dates

Month arithmetic becomes especially interesting when the starting date is near the end of a month.

For example:

SELECT DATE_ADD('2024-01-31', INTERVAL 1 MONTH);

The result isn’t February 31, because that date doesn’t exist.

MySQL adjusts the result to the last valid day of the target month.

This is important when working with:

  • Monthly subscriptions
  • Billing cycles
  • Financial reporting
  • Loan schedules
  • Recurring payments
  • Contract dates

You should always test end-of-month scenarios if your application depends on precise calendar calculations.

DATE_ADD() Works with More Than Months

Although we’re focusing on months, DATE_ADD() supports several time units.

For example, add days:

SELECT DATE_ADD(sales_date, INTERVAL 7 DAY)
FROM sales;

Add weeks:

SELECT DATE_ADD(sales_date, INTERVAL 2 WEEK)
FROM sales;

Add years:

SELECT DATE_ADD(sales_date, INTERVAL 1 YEAR)
FROM sales;

You can also work with hours, minutes, and seconds when working with date-time values.

The general pattern remains:

DATE_ADD(date, INTERVAL value unit)

DATE_ADD() vs DATE_SUB()

What if you need to move the date backward instead of forward?

That’s where DATE_SUB() comes in.

For example, subtract three months:

SELECT DATE_SUB(sales_date, INTERVAL 3 MONTH) AS three_months_before
FROM sales;

So the basic rule is:

DATE_ADD() → move forward
DATE_SUB() → move backward

For example:

DATE_ADD('2024-05-19', INTERVAL 3 MONTH)

returns:

2024-08-19

while:

DATE_SUB('2024-05-19', INTERVAL 3 MONTH)

returns:

2024-02-19

Using DATE_ADD() in a WHERE Clause

You can also use date arithmetic when filtering records.

Suppose you want to find sales that occurred within three months of a particular date.

You could build conditions using DATE_ADD():

SELECT *
FROM sales
WHERE sales_date <= DATE_ADD('2024-01-01', INTERVAL 3 MONTH);

This calculates the date three months after January 1 and uses it as part of the filter.

Date functions become particularly powerful when combined with WHERE, GROUP BY, and aggregation functions.

A Real-World Example: Subscription Renewals

Imagine a subscription table:

customer_id | start_date

If every subscription lasts three months, you could calculate the renewal date with:

SELECT customer_id,
       start_date,
       DATE_ADD(start_date, INTERVAL 3 MONTH) AS renewal_date
FROM subscriptions;

Now each customer gets a calculated renewal date.

For example:

customer_id | start_date | renewal_date
------------|------------|-------------
101         | 2024-01-15 | 2024-04-15
102         | 2024-02-20 | 2024-05-20
103         | 2024-03-10 | 2024-06-10

The same approach can be used for membership expiration dates, warranty periods, trial periods, and contract renewals.

Calculating a Future Reporting Period

Another common use is creating future reporting dates.

Suppose a company wants to calculate a date six months after each transaction:

SELECT sales_date,
       DATE_ADD(sales_date, INTERVAL 6 MONTH) AS six_month_date
FROM sales;

This can help analysts prepare:

  • Follow-up periods
  • Forecasting windows
  • Customer retention periods
  • Review dates
  • Campaign schedules
  • Financial reporting periods

Don’t Confuse DATE_ADD() with Adding Days

Adding one month isn’t always the same as adding a fixed number of days.

For example:

DATE_ADD(sales_date, INTERVAL 1 MONTH)

means one calendar month later.

It does not simply mean “add 30 days.”

That’s an important distinction in business applications where calendar months matter.

For example, January has 31 days, February can have 28 or 29, and other months have 30 or 31 days.

When your requirement is specifically “one calendar month later,” use:

INTERVAL 1 MONTH

rather than trying to approximate it with a number of days.

Quick Reference

Here are some common examples:

-- Add 1 month
DATE_ADD(sales_date, INTERVAL 1 MONTH)

-- Add 3 months
DATE_ADD(sales_date, INTERVAL 3 MONTH)

-- Add 6 months
DATE_ADD(sales_date, INTERVAL 6 MONTH)

-- Add 12 months
DATE_ADD(sales_date, INTERVAL 12 MONTH)

-- Add 1 year
DATE_ADD(sales_date, INTERVAL 1 YEAR)

-- Add 30 days
DATE_ADD(sales_date, INTERVAL 30 DAY)

And for subtraction:

DATE_SUB(sales_date, INTERVAL 3 MONTH)

Final Takeaway

Adding months to a date in MySQL is simple once you understand the DATE_ADD() function.

The basic syntax is:

DATE_ADD(date, INTERVAL number MONTH)

For example:

SELECT sales_date,
       DATE_ADD(sales_date, INTERVAL 3 MONTH) AS three_months_later
FROM sales;

The biggest advantage is that MySQL handles the calendar calculation for you, including transitions between years and dates near the end of a month.

For data analysts, developers, and database users, DATE_ADD() is a valuable tool for working with subscriptions, sales, reporting periods, contracts, renewals, forecasts, and other time-based data.

Once you become comfortable with DATE_ADD(), DATE_SUB(), and MySQL’s other date functions, many seemingly complicated date calculations become simple SQL expressions.

Tags:

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

13 − five =