How the ADD_MONTHS() function works in Mariadb?

The ADD_MONTHS() function in MariaDB is a date function that allows you to add a specified number of months to a date or datetime value, returning a new date or datetime value.

Syntax

The syntax for the MariaDB ADD_MONTHS() function is as follows:

ADD_MONTHS(date, number_of_months)

date is the date or datetime value to which you want to add months, and number_of_months is the number of months you wish to add. The function returns the resulting date or datetime value after adding the specified number of months.

Examples

Adding Months to a Date

To demonstrate adding months to a date:

SELECT ADD_MONTHS('2023-03-16', 2);

2023-05-16

This statement adds 2 months to March 16, 2023, resulting in May 16, 2023.

Adding a Negative Number of Months

You can also subtract months by using a negative number:

SELECT ADD_MONTHS('2023-03-16', -2);

2023-01-16

This subtracts 2 months from March 16, 2023, giving us January 16, 2023.

Adding Months to the End of a Month

When adding months results in a date that exceeds the number of days in the resulting month, the function adjusts the day to the last day of the month:

SELECT ADD_MONTHS('2023-01-31', 1);

2023-02-28

This adds 1 month to January 31, 2023, resulting in February 28, 2023, since February has only 28 days.

Adding Months Across Years

Adding months can also span across years:

SELECT ADD_MONTHS('2023-11-30', 2);

2024-01-30

This adds 2 months to November 30, 2023, resulting in January 30, 2024.

Handling Leap Years

The function correctly handles leap years:

SELECT ADD_MONTHS('2024-01-31', 1);

2024-02-29

This adds 1 month to January 31, 2024, resulting in February 29, 2024, since 2024 is a leap year.

Here are a few functions related to MariaDB’s ADD_MONTHS():

MariaDB SUBDATE() function is used to subtract a specified time interval from a date.
MariaDB DATE_ADD() function adds a specified time interval to a date.
MariaDB LAST_DAY() function returns the last day of the month for a given date.

Conclusion

The ADD_MONTHS() function is a useful feature in MariaDB for manipulating date and datetime values. It simplifies the process of adding or subtracting months to a given date, taking into account the varying number of days in different months and leap years. Whether you’re calculating expiration dates, scheduling future events, or performing monthly data analysis, ADD_MONTHS() provides a straightforward solution for your SQL queries. Remember to consider the specifics of the calendar when using this function to ensure accurate date calculations.