DATE_ADD and DATE_SUB in MySQL

Introduction

These functions help you add or subtract a specific amount of time to a date. This is useful when you want to find dates before or after a given date.

To find a date 10 days after today, like a deadline.

To calculate a date 3 months before a customer's subscription ends.

To add 1 year to a birthdate to find the next birthday.

To subtract 7 days from an event date to send reminders.

To adjust dates for scheduling or reporting purposes.

Syntax

MySQL

DATE_ADD(date, INTERVAL value unit)
DATE_SUB(date, INTERVAL value unit)

The 'date' is the starting date you want to change.

The 'value' is how much time you add or subtract, and 'unit' can be DAY, MONTH, YEAR, etc.

Examples

Adds 10 days to June 1, 2024, resulting in June 11, 2024.

MySQL

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

Subtracts 2 months from June 1, 2024, resulting in April 1, 2024.

MySQL

SELECT DATE_SUB('2024-06-01', INTERVAL 2 MONTH);

Adds 1 year to today's date.

MySQL

SELECT DATE_ADD(CURDATE(), INTERVAL 1 YEAR);

Subtracts 7 days from June 15, 2024, resulting in June 8, 2024.

MySQL

SELECT DATE_SUB('2024-06-15', INTERVAL 7 DAY);

Sample Program

This query shows the date 5 days after June 10, 2024, and the date 3 months before June 10, 2024.

MySQL

SELECT
  DATE_ADD('2024-06-10', INTERVAL 5 DAY) AS plus_five_days,
  DATE_SUB('2024-06-10', INTERVAL 3 MONTH) AS minus_three_months;

Important Notes

You can use many units like SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR.

If you add or subtract months or years, the day part adjusts automatically if needed.

CURDATE() returns today's date and can be used with these functions.

Summary

DATE_ADD adds time to a date; DATE_SUB subtracts time from a date.

Use INTERVAL with a value and unit to specify how much time to add or subtract.

These functions help with date calculations like deadlines, reminders, and schedules.