DIFFERENCE BETWEEN INTERVAL AND ADD_MONTHS IN TERADATA

difference-between-INTERVAL-and-ADD_MONTHS-in-teradata

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.  

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>

Teradata-Interval-function-usage

In same way, following query returns ‘2015-03-01’.

SEL CAST('2016-03-01' AS DATE ) - INTERVAL '1' YEAR</span>

Teradata-Interval-function

What is the output if I use following query?

SEL CAST('2016-02-29’ AS DATE ) - INTERVAL '1' YEAR</span>

Teradata-Interval-function-error

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)

Teradata-add_months-function-leap-year

The following query also returns ‘2015-02-28’

SEL ADD_MONTHS(CAST('2016-02-28' AS DATE ),-12)

Teradata-add_months-function

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.

Comments

comments

Leave a Reply