ADD MONTHS in Teradata

ADD_MONTHS is a special functionality in Teradata to increase/decrease the months to/from the input date/time.
Syntax : ADD_MONTHS('YYYY-MM-DD' , n)

n -(integer) can be positive or negative
SELECT ADD_MONTHS ('2000-08-15' , 1);

Result: 2000-09-15

SELECT ADD_MONTHS ('2000-08-15' , 2);

Result: 2000-10-15

SELECT ADD_MONTHS ('2000-08-15' , -2);

Result: 2000-06-15

Forgetcode requests user to have a deep look in the following codes.
ADD_MONTHS functionality will concentrate fully on months and partial for the days.

If you run the below code, you may expect the date 2000-08-31. But it won't be.
In this case Teradata believes that the user tries to find only the month(s) before/after that the input date.
Since the date 2000-08-30 is absolutely correct with date logic, it will be returned as output.
SELECT ADD_MONTHS ('2000-09-30' , -1);

Result: 2000-08-30

If you see the below case, you will realize the brilliancy of Teradata which returns 2000-09-30 and not 2000-09-31.
Teradata automatically arrange this by the universal date logic.
SELECT ADD_MONTHS ('2000-08-31' , 1);

Result: 2000-09-30


Example:
To list all male employees currently over 50 years of age, the following statement might be entered.
SELECT name, dob
FROM employee
WHERE CURRENT_DATE > ADD_MONTHS (dob, 12*50)
AND sex = ’M’ ;

To project a date three months from the date of birth of employee Roselin, enter:
SELECT name, ADD_MONTHS (dob,3)
FROM employee
WHERE name = ’Roselin’ ;


By using INTERVAL function you can add months.

Syntax:
SELECT DATE + INTERVAL 'n' MONTH


n -must be positive

Example:
SELECT CAST('2011-01-22' AS DATE) + INTERVAL '1' MONTH


Output:
1 month is added
02/22/2011