A Comprehensive Guide to Using the DATE_ADD() Function in MySQL

The DATE_ADD() function is a versatile tool in MySQL that allows you to add or subtract intervals of time to DATE or DATETIME values. Whether you're adding days, months, years, or even hours, minutes, and seconds, this function provides a streamlined way to manipulate date-time values effectively. Below, we'll break down the DATE_ADD() function, its syntax, and practical examples to help you master its use.

---

Understanding the Syntax

The basic syntax for the DATE_ADD() function is as follows:

DATE_ADD(start_date, INTERVAL expr unit)

• start_date: The initial DATE or DATETIME value.
• expr: The quantity of the time interval to be added (or subtracted, if negative).
• unit: The type of interval, such as DAY, MONTH, YEAR, HOUR, MINUTE, etc.

For instance:

SELECT DATE_ADD('2023-01-01', INTERVAL 10 DAY);

This adds 10 days to January 1, 2023, resulting in 2023-01-11.

---

Step-by-Step Examples

1. Adding Days

To add or subtract days from a date:

SELECT DATE_ADD('2023-01-01', INTERVAL 5 DAY);
SELECT DATE_ADD('2023-01-01', INTERVAL -5 DAY);

The first query adds 5 days, resulting in 2023-01-06, while the second subtracts 5 days, yielding 2022-12-27​

MySQL Tutorial

​

Delft Stack

.

---

2. Working with Months and Years

When working with larger intervals:

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

Adding a month to January 31 adjusts the date to February 28 or 29, depending on whether it's a leap year.

---

3. Combining Time Units

You can combine different units such as hours and minutes:

SELECT DATE_ADD('2023-01-01 10:00:00', INTERVAL '2:30' HOUR_MINUTE);

This adds 2 hours and 30 minutes, returning 2023-01-01 12:30:00.

---

4. Subtracting Time

Use negative intervals to subtract time:

SELECT DATE_ADD('2023-01-01 10:00:00', INTERVAL -1 HOUR);

This subtracts 1 hour, resulting in 2023-01-01 09:00:00.

---

Using DATE_ADD() with Tables

You can apply the DATE_ADD() function in UPDATE or SELECT statements for table data. Consider a trips table:

CREATE TABLE trips (
  id INT AUTO_INCREMENT PRIMARY KEY,
  destination VARCHAR(50),
  departure DATETIME
);

INSERT INTO trips (destination, departure) VALUES
('New York', '2023-01-01 09:00:00'),
('London', '2023-01-05 14:00:00');

To add 3 days to all departure dates:

UPDATE trips
SET departure = DATE_ADD(departure, INTERVAL 3 DAY);

---

Error Handling

• Invalid Dates: If start_date is invalid, DATE_ADD() will return NULL. For example:

  
  SELECT DATE_ADD('2023-02-30', INTERVAL 1 DAY);
  

  This returns NULL as February 30 is not a valid date​

  MySQL Tutorial
  ​
  Delft Stack
  .

• Overflow/Underflow: Adding months to dates may result in the day being adjusted if the target month has fewer days.

---

Best Practices

• Data Validation: Always validate start_date to ensure it’s a valid DATE or DATETIME value.
• Combining Units: Use string intervals for combinations like DAY_HOUR or HOUR_MINUTE.
• Indexing: If you're using DATE_ADD() in queries with conditions (e.g., WHERE), ensure the column is indexed for better performance.