ADD_MONTHS()

This page is part of MariaDB's Documentation.

The parent of this page is: Functions for MariaDB Enterprise Server

Overview

In 11.4 ES, 10.6 ES, 10.6 CS:: Returns a date after adding the specified number of months to the specified date or datetime value.

In 11.4 ES, 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.5 CS, 10.4 ES, 10.4 CS, 10.3 ES, 10.3 CS, 10.2 ES, 10.2 CS:: Not present

In 10.5 ES, 10.4 ES:: Not present

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.

SYNONYMS

SCHEMA

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.

SKYSQL

PRIVILEGES

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'

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series	History
11.4 Enterprise	Not present.
10.6 Enterprise	Added in MariaDB Enterprise Server 10.6.4-1.
10.6 Community	Added in MariaDB Community Server 10.6.1.
10.5 Enterprise	Not present.
10.5 Community	Not present.
10.4 Enterprise	Not present.
10.4 Community	Not present.
10.3 Enterprise	Not present.
10.3 Community	Not present.
10.2 Enterprise	Not present.
10.2 Community	Not present.

Release Series	History
11.4 Enterprise	Not present.
10.6 Enterprise	Added in MariaDB Enterprise Server 10.6.4-1.
10.5 Enterprise	Not present.
10.4 Enterprise	Not present.

EXTERNAL REFERENCES

Additional information on this topic may be found in the MariaDB Public Knowledge Base.