MySQL DATE_ADD() Function

 The DATE_ADD() function in MySQL is used to add a specified time interval (such as days, months, or years) to a date. It allows you to manipulate date and time values easily by adding or subtracting time intervals.

Syntax

DATE_ADD(date, INTERVAL value unit)

• date: The starting date to which the interval is added.
• value: The amount of time to add (can be a positive or negative number).
• unit: The unit of the time interval (e.g., DAY, MONTH, YEAR, HOUR, MINUTE, SECOND).

Common Units

• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR

Example 1: Add Days

Add 10 days to a specific date.

SELECT DATE_ADD('2024-12-23', INTERVAL 10 DAY);

Result: 2024-12-33 (which will be adjusted to 2024-12-30 due to the overflow in days).

Example 2: Add Months

Add 2 months to a specific date.

SELECT DATE_ADD('2024-12-23', INTERVAL 2 MONTH);

Result: 2025-02-23.

Example 3: Add Years

Add 1 year to a specific date.

SELECT DATE_ADD('2024-12-23', INTERVAL 1 YEAR);

Result: 2025-12-23.

Example 4: Add Hours

Add 5 hours to a specific date and time.

SELECT DATE_ADD('2024-12-23 08:00:00', INTERVAL 5 HOUR);

Result: 2024-12-23 13:00:00.

Example 5: Add Negative Interval (Subtraction)

Subtract 1 week (negative interval) from a specific date.

SELECT DATE_ADD('2024-12-23', INTERVAL -1 WEEK);

Result: 2024-12-16.

Example 6: Add Multiple Units

You can also combine multiple intervals in a single DATE_ADD() function. For example, add 1 month and 10 days:

SELECT DATE_ADD('2024-12-23', INTERVAL 1 MONTH + 10 DAY);

Result: 2025-01-02.

Important Notes

• DATE_ADD() returns a DATE, DATETIME, or TIMESTAMP, depending on the input type.
• If you use a DATETIME or TIMESTAMP, the result will include the time part as well.
• Negative intervals can be used for subtraction.

Let me know if you need more specific examples!