PROVE IT !!If you know it then PROVE IT !! Skill Proficiency Test

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

  1. SELECT ADD_MONTHS ('2000-08-15' , 1);
Result: 2000-09-15
  1. SELECT ADD_MONTHS ('2000-08-15' , 2);
Result: 2000-10-15
  1. SELECT ADD_MONTHS ('2000-08-15' , 2);
Result: 2000-06-15


Real time usage Scenario

Scenario 1)  Difference Between ADD_MONTHS vs INTERVAL

The difference between using INTERVAL and ADD_MONTHS

SEL CAST('2013-08-29' AS DATE ) -INTERVAL '6' MONTH returns Invalid Date

SEL ADD_MONTHS(CAST('2013-08-29' AS DATE ),-6)         returns 2013-02-28

Resolution  :  Interval is a data type in Teradata that represent displacement between two points in time.
ADD_MONTHS adds the months and years (Months * 12) to a specific date, while using Interval you can add YEAR, MONTH, DAY, HOUR, MINUTE AND EVEN Seconds to another interval.

Here ADD_MONTHS intelligently identifies the Leap year and returns the correct date for example
SEL ADD_MONTHS(CAST('2013-08-29' AS DATE ),-6)   :  Returns 2013-02-28
The returned date is 28 Feb as the year 2013 is not a leap year.

Where as with Interval Function :
SEL CAST('2013-08-29' AS DATE ) -INTERVAL '6' MONTH    :  Returns Invalid Date
It returns 2013-02-29, thats is actually invalid because 2013 is not a leap year.
So you can try Interval with any other date which do not return a leap FEB. for that you might have to perform some special calculation and ADD_MONTHS is better in that case.

Conclusion : Its always recommended to use ADD_MONTHS function for calculating any month based data extraction, Since Interval function displace exactly between the period to be displaced.




Add a Comment

Your email address will not be published. Required fields are marked *