MySQL DATE_ADD() Function

  1. Overview
  2. MYSQL
  3. Date Functions
  4. MySQL DATE_ADD() Function

The DATE_ADD() function in MySQL is used to add a specified time/date interval to a date and return the new date. It is commonly used for date arithmetic operations such as adding days, months, years, or other time intervals to a given date.

Definition and Usage

  • The DATE_ADD() function adds a time/date interval to a date and returns the result as a new date.

Syntax

DATE_ADD(date, INTERVAL value addunit)

Parameter Values

  • date: The date to which the interval is to be added.
  • value: The value of the interval to add. This can be positive (to add) or negative (to subtract).
  • addunit: The unit of the time interval to add. Some possible values include:
    • MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR
    • Other more specific intervals like SECOND_MICROSECOND, MINUTE_MICROSECOND, etc.

Technical Details

  • Works in: From MySQL 4.0
  • Return Value: A new date with the added or subtracted interval.

Examples

Example 1: Add 10 days to a date

SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY);

Output:

2017-06-25

This query adds 10 days to 2017-06-15, returning 2017-06-25.

Example 2: Add 15 minutes to a datetime

SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE);

Output:

2017-06-15 09:49:21

This query adds 15 minutes to the given datetime.

Example 3: Subtract 3 hours from a datetime

SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);

Output:

2017-06-15 06:34:21

This query subtracts 3 hours from 2017-06-15 09:34:21, returning 2017-06-15 06:34:21.

Example 4: Subtract 2 months from a date

SELECT DATE_ADD("2017-06-15", INTERVAL -2 MONTH);

Output:

2017-04-15

This query subtracts 2 months from 2017-06-15, returning 2017-04-15.

Use Cases

  • Date Adjustments: Used in applications where you need to adjust dates for scheduling or calculations (e.g., adding days to a due date).
  • Date Range Calculations: Helps to find the start or end date of a range by adding or subtracting time intervals.
  • Financial Applications: Used to calculate payment due dates by adding months or years to a given date.

Related Functions

  • DATE_SUB(): Subtracts a time/date interval from a date.
  • TIMESTAMPADD(): Adds an interval to a date, but supports various time units.
  • NOW(): Returns the current date and time, which can be used with DATE_ADD() to calculate future timestamps.
Was this article helpful?