ADD_MONTHS
ADD_MONTHS is a SQL function used to add a specific number of months to a date. The result is the same date, but n months later. If the original date was the last day of the month or if the resulting date would be beyond the last day of a month, it returns the last day of the resulting month.
ADD_MONTHS(date, integer)
- date: This parameter indicates the initial date to which the calculation will be applied. It should be a value that is convertible to a DATE type in Oracle SQL.
- integer: This refers to the number of months to be added to the date provided. It is a numeric value that can be both positive (to add months) and negative (to subtract months). It should be an integer value.
Example
SELECT ADD_MONTHS(TO_DATE('2020/10/27', 'yyyy/mm/dd'), 3) FROM dual;Output
'27-JAN-21'Explanation
The ADD_MONTHS function in Oracle accepts date and integer as arguments and returns the date that is the integer number of months after the given date. In the above example, ADD_MONTHS is used to add 3 months to the date ‘2020/10/27’. The result is ‘27-JAN-21’.
ADD_MONTHS(date, integer)
- date: This parameter represents the original date input for which the function will operate. It could be in the SQL Server recognized date format.
- integer: This denotes the number of months to be added to the original date input. It can be both positive or negative. A positive value will increment the months whereas a negative value will decrement the months from the original date.
Example
SELECT DATEADD(MONTH, 3, '2022-01-01') AS DateAddMonths;Output
2022-04-01 00:00:00.000Explanation
In the given example, 3 months have been added to the date ‘2022-01-01’. The DATEADD function in SQL server is used to this effect, which gives the output as ‘2022-04-01’.