Compatibility: Teradata Extension

The Teradata ADD_MONTHS function can be used to calculate a new date. This date may be in the future (addition) or in the past (subtraction). The calendar intelligence is built-in for the number of days in a month as well as leap year processing. Since the ANSI CURRENT_DATE and CURRENT_TIME are compatible with the original DATE and TIME functions, the ADD_MONTHS works with them as well.

Below is the syntax for the ADD_MONTHS function:

The next SELECT uses literals instead of table rows to demonstrate the calendar logic used by the ADD_MONTHS function when beginning with the last day of a month and arriving at the last day of February:

1 Row ReturnedRow returned

Notice, when using the ADD_MONTHS function, that all the output displays in ANSI date form. This is true when using BTEQ or Queryman. Conversely, the date arithmetic uses the default date format. Likewise, the second ADD_MONTHS uses –8, which equates to subtraction or going back in time versus ahead. Additionally, because months have a varying number of days, the output from math is likely to be different than the ADD_MONTHS.

The next SELECT uses the ADD_MONTHS function as an alternative to the previous SELECT operations for showing the month and day of the payment due date in 2 months:

5 Rows Returned

add months


The ADD_MONTHS function also takes into account the last day of each month. The following goes from the last day of one month to the last day of another month:

1 Row Returnedlast day of one month to the last day of another month

Whether going forward or backward or backward in time, a leap year is still recognized using ADD_MONTHS.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Teradata Topics