MONTHS_ADD

Syntax

MONTHS_ADD(date, months)

Purpose

This function adds the specified number of months to the date date.

Supported data types

The months_add function in OceanBase Database supports various date-related data types as input and returns the corresponding type based on the input type. The mapping is as follows:

• Input DATE returns DATE.
• Input DATETIME returns DATETIME.
• Input TIMESTAMP returns DATETIME.
• Input VARCHAR (a string that can be parsed as a date) returns DATETIME.
• Input TIME returns DATETIME. For TIME type, the function uses the current real-time date for the year, month, and day, and then calculates with the given number of months.

Behavior

• In OceanBase Database, the month_add function returns NULL instead of throwing an error when the input is invalid.
• It can handle TIME type, using the current real-time date for the year, month, and day.
• It allows invalid dates to be calculated based on the ALLOW_INVALID_DATES setting.
• When the parameter is a string in a single SELECT statement, it is uniformly converted to DATETIME type for output.

Examples

Add 1 month to 2026-01-31.

obclient> SELECT MONTHS_ADD('2026-01-31', 1);

The result is as follows:

+-----------------------------+
| MONTHS_ADD('2026-01-31', 1) |
+-----------------------------+
| 2026-02-28 00:00:00         |
+-----------------------------+
1 row in set