Table of Contents
Days can be added to a date in MySQL by using the DATE_ADD function. This function takes in three parameters: the starting date, the number of intervals to add, and the interval type (day, month, year, etc.). For example, if we want to add 5 days to the date ‘2021-10-01’, the query would be: SELECT DATE_ADD(‘2021-10-01’, INTERVAL 5 DAY). This will return the date ‘2021-10-06’. In this way, days can be easily added to a date in MySQL.
You can use the following syntax to add a specific number of days to a date in MySQL:
SELECT sales_date, DATE_ADD(sales_date, INTERVAL 7 DAY) FROM sales;
This particular example creates a new column that adds 7 days 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 Days 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 7 days 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 7 DAY) FROM sales;
Output:
+------------+--------------------------------------+
| sales_date | DATE_ADD(sales_date, INTERVAL 7 DAY) |
+------------+--------------------------------------+
| 2024-02-10 | 2024-02-17 |
| 2024-11-25 | 2024-12-02 |
| 2024-07-30 | 2024-08-06 |
| 2024-01-14 | 2024-01-21 |
| 2024-05-19 | 2024-05-26 |
+------------+--------------------------------------+
Notice that the dates in the new column represent the date in the sales_date column with 7 days 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 7 DAY) AS add_seven FROM sales;
+------------+------------+
| sales_date | add_seven |
+------------+------------+
| 2024-02-10 | 2024-02-17 |
| 2024-11-25 | 2024-12-02 |
| 2024-07-30 | 2024-08-06 |
| 2024-01-14 | 2024-01-21 |
| 2024-05-19 | 2024-05-26 |
+------------+------------+
Notice that the new column is named add_seven, which is much easier to read.
Note: If you would like to subtract a certain number of days 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: