ADD_MONTHS vs INTERVAL

Database
KVB
N/A

ADD_MONTHS vs INTERVAL

Please let me know the exact 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

Regards

KVB

Tags (1)
4 REPLIES

Re: ADD_MONTHS vs INTERVAL

Hi,

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 in your case 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. 

When you try to perform the same with 

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.

Try  SEL CAST('2013-07-29' AS DATE ) -INTERVAL '6' MONTH.

Khurram

Re: ADD_MONTHS vs INTERVAL

In our case we are not getting last day of month as per below query:

SELECT ADD_MONTHS(CAST('2014-02-28' AS DATE ),-6);

OR

select ADD_MONTHS(DATE '2014-02-28',-6)

We are getting {8/28/2013} as the result. Is there any other function which gives us last day 6 months back i.e. {8/31/2013} as our output.

Re: ADD_MONTHS vs INTERVAL

SELECT ADD_MONTHS(CAST('2014-02-28' AS DATE )- extract (day from CAST('2014-02-28' AS DATE )) ,-6);

N/A

Re: ADD_MONTHS vs INTERVAL

AnkitIsLucky - Try the code below.

select add_months(date '2014-02-28' + 1, -6) - 1

The idea is to use the 1st of the month as the reference for calculating the month backwards.

The problem with using the last date of the month as it is while calculating date backwards is that not all months have the same number of days, hence, using the 1st of the month for such calculations presents an easier approach.