ADD_MONTHS()

Overview

In 10.6 ES:

Returns a date after adding the specified number of months to the specified date or datetime value.

In 10.5 ES, 10.4 ES:

Not present

See also: Functions in 10.6 ES, in 10.5 ES, and in 10.4 ES

USAGE

ADD_MONTHS(date,count)

DETAILS

  • ADD_MONTHS() is a date-time function that adds the specified number of months to the specified date and returns the resulting date.

  • ADD_MONTHS() subtracts the specified number of months when the count is negative.

  • ADD_MONTHS() preserves the day of the month from the starting date in the resulting month as long as the resulting date is valid, otherwise the day is rounded down to fit within the month.

  • ADD_MONTHS() preserves the time of day in the returned value if the "date" value has a time element.

  • ADD_MONTHS() implicitly converts the first argument to a DATE or DATETIME type, if convertible.

  • ADD_MONTHS() implicitly rounds the second argument to the nearest integer, if convertible.

  • ADD_MONTHS() returns NULL if any argument is NULL.

PARAMETERS

date

The value expression for a date or datetime. This value affects the type of the return value, which will be a DATE or DATETIME value depending on if the input value has a time portion.

count

The value expression for the number of months to add to the date argument. The value may be a +count or -count value, with a negative value moving backward in time.

EXAMPLES

Simple Example

The following example shows that string arguments are implicitly converted to a DATE type and shows how different input affects the output:

SELECT ADD_MONTHS('2021-01-10',12),
       ADD_MONTHS('2021-01-31', 1),
       ADD_MONTHS('2021-01-31', 2),
       ADD_MONTHS('2021-01-31', 3),
       ADD_MONTHS('2021-01-31', 4),
       ADD_MONTHS('2021-01-31',-1),
       ADD_MONTHS('2021-01-31',-2)\G
*************************** 1. row ***************************
ADD_MONTHS('2021-01-10',12): 2022-01-10
ADD_MONTHS('2021-01-31', 1): 2021-02-28
ADD_MONTHS('2021-01-31', 2): 2021-03-31
ADD_MONTHS('2021-01-31', 3): 2021-04-30
ADD_MONTHS('2021-01-31', 4): 2021-05-31
ADD_MONTHS('2021-01-31',-1): 2020-12-31
ADD_MONTHS('2021-01-31',-2): 2020-11-30

Example Schema and Data

Some of the examples on this page use the date_time table, which has the following schema and data:

CREATE TABLE date_time (
   date1 DATE,
   date2 DATETIME,
   date3 TIMESTAMP
);
INSERT INTO date_time VALUES
  ('1997-01-31', '1997-01-31 12:00:00', '1997-01-31 12:00:00'),
  ('2001-12-01', '2001-12-01 16:00:00', '2001-12-01 16:00:00'),
  ('2020-02-29', '2020-02-29 12:10:00', '2020-02-29 12:10:00');

Per-row Values

The following example shows how to call the ADD_MONTHS() function with a table column as an argument:

SELECT date1, ADD_MONTHS(date1, 2),
       date2, ADD_MONTHS(date2, 1.2),
       date3, ADD_MONTHS(date3, -1)
FROM date_time\G
*************************** 1. row ***************************
                 date1: 1997-01-31
  ADD_MONTHS(date1, 2): 1997-03-31
                 date2: 1997-01-31 12:00:00
ADD_MONTHS(date2, 1.2): 1997-02-28 12:00:00
                 date3: 1997-01-31 12:00:00
 ADD_MONTHS(date3, -1): 1996-12-31 12:00:00
*************************** 2. row ***************************
                 date1: 2001-12-01
  ADD_MONTHS(date1, 2): 2002-02-01
                 date2: 2001-12-01 16:00:00
ADD_MONTHS(date2, 1.2): 2002-01-01 16:00:00
                 date3: 2001-12-01 16:00:00
 ADD_MONTHS(date3, -1): 2001-11-01 16:00:00
*************************** 3. row ***************************
                 date1: 2020-02-29
  ADD_MONTHS(date1, 2): 2020-04-29
                 date2: 2020-02-29 12:10:00
ADD_MONTHS(date2, 1.2): 2020-03-29 12:10:00
                 date3: 2020-02-29 12:10:00
 ADD_MONTHS(date3, -1): 2020-01-29 12:10:00

ERROR HANDLING

Incorrect Datetime Value

When the ADD_MONTHS() function is called and the first argument is invalid, the function will raise a warning with the ER_TRUNCATED_WRONG_VALUE error code and will return NULL. The first argument is invalid when it is not a DATETIME or DATE value, and it is not implicitly convertible to a DATETIME or DATE value.

In the following example, the first argument is of type TIME, which cannot be converted to a DATETIME or DATE value. Details about the warning can be listed with SHOW WARNINGS statement:

SELECT ADD_MONTHS('12:10:00',1);
+--------------------------+
| ADD_MONTHS('12:10:00',1) |
+--------------------------+
| NULL                     |
+--------------------------+
Warning (Code 1292): Incorrect datetime value: '2012-10-00'

CHANGE HISTORY

Release Series

History

10.6 Enterprise

  • Added in MariaDB Enterprise Server 10.6.4-1.

10.5 Enterprise

  • Not present.

10.4 Enterprise

  • Not present.

EXTERNAL REFERENCES