MySQL - DATE_ADD() Function

---

---

MySQL - DATE_ADD() Function

The MySQL DATE_ADD() function is a synonym to the MySQL ADDDATE() function.

DATE_ADD() function is also used to add the specified interval to a date value. This function accepts two arguments: one is a date while the other is the interval that needs to be added to the specified date value. The result obtained will be the final date after the interval is added. A NULL value is returned if both date and interval arguments are NULL.

It is mostly helpful in scenarios where time lapse is required.

Syntax

Following is the syntax of the above function −

DATE_ADD(date, INTERVAL expr unit);

Parameters

This method accepts 3 parameters. The same is described below −

• date is the value representing the date it can be of the type String, DATE (YEAR, MONTH, and DAY), DATETIME (HOURS, MINUTES or, SECONDS) or, TIMESTAMP.

• expr is the value representing the interval value.

• unit is the interval type represented by the expr value which can be DAY, WEEK, MONTH, QUARTER, YEAR, HOUR, MINUTE, SECOND, MICROSECOND.

The unit parameter can be mixed values as: SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND, HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE, DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE, DAY_HOUR, YEAR_MONTH.

Return value

This function returns a new date or datetime value that results from adding the specified time interval to the input date.

Example

Following example demonstrates the usage of the DATE_ADD() function −

SELECT DATE_ADD('2015-09-05', INTERVAL 20 DAY);

Output

Following output is obtained −

DATE_ADD('2015-09-05', INTERVAL 20 DAY)
2015-09-25

Example

In the following example we are passing DATETIME value for date −

SELECT DATE_ADD('2018-05-23 20:40:32.88558', INTERVAL 3 WEEK);

Output

Following output is obtained −

DATE_ADD('2018-05-23 20:40:32.88558', INTERVAL 3 WEEK)
2018-06-05 20:40:32.885580

Example

We can also pass negative values as arguments to this function −

SELECT DATE_ADD('2015-09-05', INTERVAL -20 DAY);

Output

Following output is obtained −

DATE_ADD('2015-09-05', INTERVAL -20 DAY)
2015-08-16

Example

Following example demonstrates the usage of various units available in the DATE_ADD() function −

We are using the YEAR_MONTH unit with an INTERVAL expression in the following query −

SELECT DATE_ADD('2021-03-22', INTERVAL '5-6 ' YEAR_MONTH );

Following output is obtained −

DATE_ADD('2021-03-22', INTERVAL '5-6' YEAR_MONTH )
2026-09-22

We are using the WEEK unit with an INTERVAL expression in the following query −

SELECT DATE_ADD('2021-03-22', INTERVAL '3' WEEK );

Following output is obtained −

DATE_ADD('2021-03-22', INTERVAL '3' WEEK )
2021-04-12

We are using the QUARTER unit with an INTERVAL expression in the following query −

SELECT DATE_ADD('2021-03-22', INTERVAL '3' QUARTER );

Following output is obtained −

DATE_ADD('2021-03-22', INTERVAL '3' QUARTER )
2021-12-22

We are using the MINUTE_MICROSECOND unit with an INTERVAL expression in the following query −

SELECT DATE_ADD('2021-03-22', INTERVAL '10:09.2362191' MINUTE_MICROSECOND);

Following output is obtained −

DATE_ADD('2021-03-22', INTERVAL '10:09.2362191' MINUTE_MICROSECOND)
2021-03-22 00:10:11.362191

We are using the SECOND_MICROSECOND unit with an INTERVAL expression in the following query −

SELECT DATE_ADD('2021-03-22', INTERVAL '10.2362191' SECOND_MICROSECOND);

Following output is obtained −

DATE_ADD('2021-03-22', INTERVAL '10.2362191' SECOND_MICROSECOND)
2021-03-22 00:00:12.362191

We are using the MINUTE_SECOND unit with an INTERVAL expression in the following query −

SELECT DATE_ADD('2021-03-22', INTERVAL '10.21' MINUTE_SECOND);

Following output is obtained −

DATE_ADD('2021-03-22', INTERVAL '10.21' MINUTE_SECOND)
2021-03-22 00:10:21

Example

You can also pass the column name as an argument to this function. Let us create a table with name ORDERS in MySQL database using CREATE TABLE statement as shown below −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Now, insert the following records into the ORDERS table using the INSERT statement −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

Following query adds an interval of 14 days to the values in DATE column −

SELECT OID, DATE, DATE_ADD(DATE, INTERVAL 14 DAY), AMOUNT 
FROM ORDERS;

Output

The output is displayed as follows −

OID	DATE	ADDDATE(DATE, INTERVAL 14 DAY)	AMOUNT
102	2009-10-08 00:00:00	2009-10-22 00:00:00	3000.00
100	2009-10-08 00:00:00	2009-10-22 00:00:00	1500.00
101	2009-11-20 00:00:00	2009-12-04 00:00:00	1560.00
103	2008-05-20 00:00:00	2008-06-03 00:00:00	2060.00