Date Caclucations

Database

Date Caclucations

I am trying to do the following task with dates in teradata.

1) Get The date for the end of the month. 

add_months(date, 1) - Extract(Day From Date),

2) Then add 11 months to that date (which I am going to use as the end data for a between dates condiditon in the where statement). 

ADD_MONTHS(((add_months(date, 1) - Extract(Day From Date))), 11),

If I am workign with June 2014 would expect this caculation to produce 5/31/15.  However it produces 5/30/15. 

When I try it for May 2014 I get error 2665: Invalid Date 

add_months('2014-05-15', 1) - Extract(Day From '2014-05-15'),
ADD_MONTHS(((add_months( '2014-05-15', 1) - Extract(Day From  '2014-05-15'))), 11),

Can anyone explain why this happens and how to fix it. 

Tags (1)
1 REPLY
Senior Apprentice

Re: Date Caclucations

You add 11 months to 2014-06-30 which results in 2015-05-30.

This is correct based on Teradata's algorithm, but different from Oracle, ADD_MONTHS is no Standard SQL :-)

You need to find the first of a month, add 12 months and the substract 1 day:

ADD_MONTHS(DATE - (EXTRACT(DAY FROM DATE)-1),12)-1

If you're on TD14 you might also use oADD_MONTH which replicates Oracle's implemenation.