0

Hi,

I am facing some challenges with Add months function in TD, in Oracle the date range conditions are derived based on date value calculating the end of the month and then adding the months with -13 and then adding one day like +1 For EG: in Oracle,

Select ADD_MONTHS(LAST_DAY(TO_DATE('01-06-2010','DD-MM-YYYY')+1),-13)+1

from dual;

Results: 6/1/2009

The same logic converted in TD based on Last day of month, but having some challenges as in TD the add_months does 30 days for all the months, do we have any other way to do this. Please help me out how to handle this.

In TD, converted like Oracle based on Last day of month,

Select add_months(add_months((cast('01-06-2010' as date format 'DD-MM-YYYY')+1 - extract(day from cast('01-06-2010' as date format 'DD-MM-YYYY')+1)+1),1)-1,-13)+1

Results: 5/31/2009 but supposed to be 6/1/2009

If I calculate the same logic with first day of month, the values match for this parameter, but comes different for other parameters.

In TD, converted like Oracle based on First day of month,

select Add_Months(cast('01-06-2010' as date format 'DD-MM-YYYY')+1 - Extract(day from Cast('01-06-2010' as date format 'DD-MM-YYYY')+1),-12)+1

Results: 6/1/2009 like Oracle

but with different parameter, like '30-11-2009', Teradata calculating with Last day matches, but first day fails

Oracle:Select ADD_MONTHS(LAST_DAY(TO_DATE('30-11-2009','DD-MM-YYYY')+1),-13)+1 from dual Results: 12/1/2008 Teradata with Last day:select add_months(add_months((cast('30-11-2009' as date format 'DD-MM-YYYY')+1 - extract(day from cast('30-11-2009' as date format 'DD-MM-YYYY')+1)+1),1)-1,-13)+1 Results: 12/01/2008

Please help me to handle with Add_months. Thanks in Advance for your help.

Thanks, TDHElp

flag

1 Answer

0

The following seem to work for me. I used the ANSI date format to reduce the clutter that the CAST() function was introducing.

SELECT ADD_MONTHS(DATE '2010-11-30' - EXTRACT(DAY FROM DATE '2010-11-30'), -12) + 1; /* 2009-11-01 */
SELECT ADD_MONTHS(ADD_MONTHS(DATE '2010-11-30', 1) - EXTRACT(DAY FROM DATE '2010-11-30') + 1, -13) - 1; /* 2009-10-31 */

SELECT ADD_MONTHS(DATE '2010-01-06' - EXTRACT(DAY FROM DATE '2010-01-06'), -12) + 1; /* 2009-01-01 */
SELECT ADD_MONTHS(ADD_MONTHS(DATE '2010-01-06', 1) - EXTRACT(DAY FROM DATE '2010-01-06') + 1, -13); /* 2008-12-31 */
link|flag

Your Answer

Get an OpenID
or

Not the answer you're looking for? Browse other questions tagged or ask your own question.