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.
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.