MySQL - ADDDATE() Function

The DATE, DATETIME and TIMESTAMP datatypes in MySQL are used to store the date, date and time, time stamp values respectively. These values are known as temporal values, as they are instant values that keep changing with varying precision (like, temporary values). MySQL provides a set of functions to manipulate these values.

MySQL ADDDATE() Function

The MySQL ADDDATE() function is 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 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.

Syntax

Following is the syntax of the above function −

ADDDATE(date, INTERVAL expr unit);

Parameters

Following are the parameters accepted by this function −

  • 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 after adding the specified interval to the original date.

Another Syntax

This function has another syntax as shown below. In here we provide an "expression" representing the date and "days" as the interval representing the number of days that is to be added to the given date −

ADDDATE(expr, days);

Example

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

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

Output

Following output is obtained −

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

Example

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

SELECT ADDDATE('2018-05-23 20:40:32.88558', '13 6:4:1.222222');

Output

Following output is obtained −

ADDDATE('2018-05-23 20:40:32.88558', '13 6:4:1.222222')
2018-06-05 20:40:32.885580

Example

Following example demonstrates the second syntax of this function where we pass the number of days to be added as an interval −

SELECT ADDDATE('1995-11-15', 554);

Output

Following output is obtained −

ADDDATE('1995-11-15', 554)
1997-05-22

Example

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

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

In another query, let us pass the negative interval value without using the INTERVAL keyword −

SELECT ADDDATE('2015-09-05', -20) AS ADDDATE;

Output

Following output is obtained −

ADDDATE
2015-08-16

Example

Following example demonstrates the usage of various units available in the ADDDATE() function.

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

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

Following output is obtained −

ADDDATE('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 ADDDATE('2021-03-22', INTERVAL '3' WEEK );

Following output is obtained −

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

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

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

Following output is obtained −

ADDDATE('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 ADDDATE('2021-03-22', INTERVAL '10:09.2362191' MINUTE_MICROSECOND);

Following output is obtained −

ADDDATE('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 ADDDATE('2021-03-22', INTERVAL '10.2362191' SECOND_MICROSECOND);

Following output is obtained −

ADDDATE('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 ADDDATE('2021-03-22', INTERVAL '10.21' MINUTE_SECOND);

Following output is obtained −

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

Example

In this example, we have created a table named ORDERS using the following CREATE TABLE query −

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, ADDDATE(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

Example

We can also add an interval without specifying the INTERVAL keyword. In this example, following query adds 225 days to the values of the DATE column −

SELECT OID, DATE, ADDDATE(DATE, 225), AMOUNT FROM ORDERS;

Output

The output is displayed as follows −

OID DATE ADDDATE(DATE, INTERVAL 255) AMOUNT
102 2009-10-08 00:00:00 2010-05-21 00:00:00 3000.00
100 2009-10-08 00:00:00 2010-05-21 00:00:00 1500.00
101 2009-11-20 00:00:00 2010-07-03 00:00:00 1560.00
103 2008-05-20 00:00:00 2008-12-31 00:00:00 2060.00
Advertisements