How to Add Days to Date in MySQL (With Example)


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;

Output:

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

MySQL: How to Add Hours to Datetime
MySQL: How to Add Minutes to Datetime
MySQL: How to Calculate Difference Between Two Dates

Leave a Reply

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