Table of Contents
In MySQL, you can add months to a date using the DATE_ADD() function. The function takes in three parameters: the date you want to add months to, the number of months to add, and the unit of time (in this case, MONTH). For example, if you want to add 3 months to the date July 1st, 2020, you would use the following query: SELECT DATE_ADD(‘2020-07-01’, INTERVAL 3 MONTH). This will return the date October 1st, 2020.
You can use the following syntax to add a specific number of months to a date in MySQL:
SELECT sales_date, DATE_ADD(sales_date, INTERVAL 3 MONTH) FROM sales;
This particular example creates a new column that adds 3 months to the corresponding date in the sales_date column of the table named sales.
The following example shows how to use this syntax in practice.
Example: How to Add Months to Date in MySQL
Suppose we have the following table named sales that contains information about sales made at various grocery stores on various dates:
-- create table
CREATE TABLE sales (
store_ID INT PRIMARY KEY,
item TEXT NOT NULL,
sales_date DATE NOT NULL
);
-- insert rows into table
INSERT INTO sales VALUES (0001, 'Oranges', '2024-02-10');
INSERT INTO sales VALUES (0002, 'Apples', '2024-11-25');
INSERT INTO sales VALUES (0003, 'Bananas', '2024-07-30');
INSERT INTO sales VALUES (0004, 'Melons', '2024-01-14');
INSERT INTO sales VALUES (0005, 'Grapes', '2024-05-19');
-- view all rows in table
SELECT * FROM sales;
Output:
+----------+---------+------------+
| 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 |
+----------+---------+------------+
Suppose that we would like to create a new column that adds 3 months to each corresponding date in the sales_date column.
We can use the following syntax to do so:
SELECT sales_date, DATE_ADD(sales_date, INTERVAL 3 MONTH) FROM sales;
Output:
+------------+----------------------------------------+
| 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 that the dates in the new column represent the date in the sales_date column with 3 months added to them.
If you’d like, you can also use the AS statement to give this new column a specific name:
SELECT sales_date, DATE_ADD(sales_date, INTERVAL 3 MONTH) AS add_three FROM sales;
+------------+------------+
| 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 |
+------------+------------+
Notice that the new column is named add_three, which is much easier to read.
Note: If you would like to subtract a certain number of months from a date, then you can use the DATE_SUB() function instead.
Additional Resources
The following tutorials explain how to perform other common tasks in MySQL: