PL/SQL Add_Months

The PL/SQL Add_Months function is a powerful date manipulation function available in Oracle Database. It allows you to add or subtract a specified number of months to a given date, resulting in a new date.

Syntax

The syntax of the Add_Months function is as follows:

ADD_MONTHS (date, n)

Here, date is the input date to which you want to add or subtract months, and n is the number of months you want to add or subtract. The function returns a new date that is n months ahead or behind the input date.

The Add_Months function operates on both DATE and TIMESTAMP data types. It is especially useful when you need to calculate future or past dates based on a given reference date.

Examples

Here are a few examples to demonstrate the usage of the Add_Months function:

-- Adding 3 months to a date
DECLARE
  original_date DATE := TO_DATE('2022-01-15', 'YYYY-MM-DD');
  new_date DATE;
BEGIN
  new_date := ADD_MONTHS(original_date, 3);
  DBMS_OUTPUT.PUT_LINE('New Date: ' || new_date);
END;

Output: New Date: 2022-04-15

-- Subtracting 2 months from a timestamp
DECLARE
  original_timestamp TIMESTAMP := TO_TIMESTAMP('2022-07-01 10:30:00', 'YYYY-MM-DD HH24:MI:SS');
  new_timestamp TIMESTAMP;
BEGIN
  new_timestamp := ADD_MONTHS(original_timestamp, -2);
  DBMS_OUTPUT.PUT_LINE('New Timestamp: ' || new_timestamp);
END;

Output: New Timestamp: 2022-05-01 10:30:00.000000

In the above examples, we used the Add_Months function to add or subtract months from a given date or timestamp. The result is stored in a new variable (new_date or new_timestamp), and we can perform further operations or display the result as required.

It is important to note that the Add_Months function handles leap years and adjusts the day component of the resulting date accordingly. For example, if the original date is February 28th and you add one month, the resulting date will be March 28th (unless it’s a leap year).

In conclusion, the Add_Months function in PL/SQL is a convenient tool for manipulating dates by adding or subtracting months. It allows you to perform calculations on dates and timestamps, facilitating various date-related operations in Oracle Database applications.