Lets take a look at differences between INTERVAL and ADD_MONTHS() in Teradata. 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.
It is always better to prefer ADD_MONTHS over INTERVAL.
Why?
Let’s check one scenario.
INTERVAL Usage
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></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?
ADD_MONTHS() Usage
Try to avoid using of INTERVAL and use ADD_MONTHS function instead of INTERVAL.
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' MONTHreturns 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.
can the value after INTERVAL key word be a variable coming from a column name like below
[column1] + INTERVAL [column2]
where column1 is a date column and column2 is an integer.
similar to DATEADD function in MS SQL
I have got it to work like below
INTERVAL ’10’ DAY + CAL_TOL_DATE
now I need to refer a column valude instead of constant ’10’ next to INTERVAL keywork. like below
INTERVAL COLUMN2 + CAL_TOL_DATE. As below
COLUMN2 CAL_TOL_DATE New column(INTERVAL COLUMN2 + CAL_TOL_DATE)
6 1/02/2020 7/02/2020 add 7 days to CAL_TOL_DATE
15 20/03/2020 4/04/2020 add 15 days to CAL_TOL_DATE
65 15/12/2019 18/02/2020 add 20 days to CAL_TOL_DATE