MySQL DATE_ADD And ADDDATE Functions: Add Specified Time Interval To Date

In MySQL, the DATE_ADD() and ADDDATE() functions are used to add a time/date interval to a date and then return the date. They can be very useful when you need to calculate an expiration date, a follow-up date, or any other future or past dates.

Here's the basic syntax for DATE_ADD():

DATE_ADD(date, INTERVAL expr type)

And for ADDDATE():

ADDDATE(date, INTERVAL expr type)

Or alternatively:

ADDDATE(date, days)

In both cases, date is the starting date, expr is the amount of interval to add, and type can be one of the following values: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

Here's an example using DATE_ADD():

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

This will return '2023-01-02', which is one day after the original date.

Here's an example using ADDDATE() with the INTERVAL keyword:

SELECT ADDDATE('2023-01-01', INTERVAL 1 DAY);

This will also return '2023-01-02'.

You can also use ADDDATE() without the INTERVAL keyword, in which case it adds the specified number of days:

SELECT ADDDATE('2023-01-01', 1);

This will return '2023-01-02' as well.

These functions can be used in a WHERE clause to find data in a date range, in a SELECT clause to output calculated dates, or anywhere you need to calculate a date or datetime based on an interval.

How to Use DATE_ADD in MySQL:
- Description: DATE_ADD is a MySQL function used to add a specified time interval to a date or datetime value.
- Code:
```
-- Example of using DATE_ADD
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS next_day;
```
Adding Days to a Date in MySQL using DATE_ADD:
- Description: Add a specific number of days to a date using the DAY interval.
- Code:
```
-- Example adding 7 days to a date
SELECT DATE_ADD('2023-01-01', INTERVAL 7 DAY) AS future_date;
```
Adding Months to a Date with DATE_ADD in MySQL:
- Description: Use the MONTH interval to add months to a date.
- Code:
```
-- Example adding 3 months to a date
SELECT DATE_ADD('2023-01-01', INTERVAL 3 MONTH) AS future_date;
```
Adding Hours, Minutes, and Seconds to a Date in MySQL:
- Description: Adjust the time part of a datetime value by adding hours, minutes, and seconds.
- Code:
```
-- Example adding 2 hours and 30 minutes to a datetime
SELECT DATE_ADD('2023-01-01 12:00:00', INTERVAL 2 HOUR + 30 MINUTE) AS new_time;
```
Adding Weeks to a Date with DATE_ADD in MySQL:
- Description: Use the WEEK interval to add weeks to a date.
- Code:
```
-- Example adding 2 weeks to a date
SELECT DATE_ADD('2023-01-01', INTERVAL 2 WEEK) AS future_date;
```

MySQL DATE_ADD Function in WHERE Clause:

Description: Apply DATE_ADD in the WHERE clause to filter records based on date conditions.

Code:

-- Example selecting records with a date within the next 7 days
SELECT * FROM events WHERE event_date <= DATE_ADD(NOW(), INTERVAL 7 DAY);

Adding Years to a Date with ADDDATE in MySQL:
- Description: Use the YEAR interval to add years to a date. ADDDATE is an alternative to DATE_ADD for this purpose.
- Code:
```
-- Example adding 5 years to a date using ADDDATE
SELECT ADDDATE('2023-01-01', INTERVAL 5 YEAR) AS future_date_add;
```
Handling Negative Intervals with DATE_ADD in MySQL:
- Description: Negative intervals can be used with DATE_ADD to subtract time from a date.
- Code:
```
-- Example subtracting 3 days from a date
SELECT DATE_ADD('2023-01-01', INTERVAL -3 DAY) AS past_date;
```