How to Add Dates in MySQL

Adding dates in MySQL is a common task for database management, often requiring precision and understanding of MySQL's date and time functions. The DATE_ADD function, in particular, is essential for manipulating and calculating dates.

Understanding DATE_ADD

The DATE_ADD function in MySQL is used to add a specified time interval to a date.

Syntax

DATE_ADD(date, INTERVAL expr unit)

• date: The starting date
• expr: The value of the time interval to add
• unit: The unit of the time interval (e.g., DAY, MONTH, YEAR)

Examples of DATE_ADD Usage

Adding Days to a Date

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

This query adds 10 days to January 1, 2023.

Adding Months to a Date

SELECT DATE_ADD('2023-01-01', INTERVAL 2 MONTH);

This adds 2 months to January 1, 2023.

Adding Years to a Date

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

This example adds 1 year to January 1, 2023.

Combining DATE_ADD with Other Functions

Current Date Addition

SELECT DATE_ADD(CURDATE(), INTERVAL 15 DAY);

Adds 15 days to the current date.

Date Subtraction

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

Subtracts 3 days from January 1, 2023.

Handling Time Components

Adding Hours, Minutes, and Seconds

SELECT DATE_ADD('2023-01-01 08:30:00', INTERVAL '2:15:30' HOUR_SECOND);

Adds 2 hours, 15 minutes, and 30 seconds to the specified datetime.

Advanced DATE_ADD Usage

Adding Complex Intervals

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

Adds 1 year and 2 months to January 1, 2023.

Using DATE_ADD in Table Updates

UPDATE orders
SET delivery_date = DATE_ADD(order_date, INTERVAL 7 DAY)
WHERE order_id = 1001;

Sets the delivery date to 7 days after the order date for a specific order.

Common Mistakes and Tips

• Ensure the date format matches MySQL's standard YYYY-MM-DD or YYYY-MM-DD HH:MM:SS.
• Be cautious with leap years and month-end dates while adding months or years.
• Use the DATE_SUB function for more intuitive date subtraction.