Lets take a look at differences between INTERVAL and ADD_MONTHS() in Teradata. And this post mainly focused on where to use ADD_MONTHS() over INTERVAL function in Teradata.
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.
Let’s check one scenario.
The following query returns ‘2015-02-28’ because we asked Teradata to return 1 year back date.
SEL CAST('2016-02-28' AS DATE ) - INTERVAL '1' YEAR</span>
In same way, following query returns ‘2015-03-01’.
SEL CAST('2016-03-01' AS DATE ) - INTERVAL '1' YEAR</span>
What is the output if I use following query?
SEL CAST('2016-02-29’ AS DATE ) - INTERVAL '1' YEAR</span>
It is giving Invalid date error. Why because 2016 is a leap year and when we use INTERVAL, it blindly goes back to ‘2015-02-29’. But 2015 is not a leap year hence the invalid date error.
What is the solution here?
In this case ADD_MONTHS intelligently identifies the Leap year and returns the correct date for example:
Following query simply returns ‘2015-02-28’.
SEL ADD_MONTHS(CAST('2016-02-29' AS DATE ),-12)
The following query also returns ‘2015-02-28’
SEL ADD_MONTHS(CAST('2016-02-28' AS DATE ),-12)
When you try to perform the same with
SEL CAST('2015-08-29' AS DATE ) -INTERVAL '6' MONTH
returns error as Invalid date. It tries returns 2015-02-29, that’s actually invalid because 2013 is not a leap year.
So you can try Interval with any other date which do not return a leap February. For that you might have to perform some special calculation and ADD_MONTHS is better in that case.