how to calculate last year current month in obiee 11g?

Question

Hi,

I want to calculate last year current month in obiee 11g. i have tried in db.

----SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -12), 'YYYYMMDD') FROM DUAL;

Using the above query, last year current month is --"2015-01-08"

But i want to get same result using timestampadd() in obiee 11g.

Please help me,

Thanks in advance,

A.kavya

ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD · Answer

Hi,

As you said,

I have tried. Now its coming..

That queries are...

Last year first day of current month::

TIMESTAMPADD(SQL_TSI_YEAR, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE) )

Last year last day of current month

TIMESTAMPADD(SQL_TSI_YEAR, -1, TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) )

Thanks in advance,

A.kavya.

Sandeep Andhale · Answer

Hi Gianni Ceresa,

Beautiful answer , I am working in same area of Fusion CRM sales cloud.

regards 
Sandeep

Gianni Ceresa · Answer

Hi,

You have an example to get last year, so to have what you look for you must use TIMESTAMPADD() and DAYOFMONTH() and do some calculation ...

The key is to get the first day of the month and then move around from there.

With DAYOFMONTH() you can have the number corresponding to the day of month of the date you pass as parameter, so by using TIMESTAMPADD to subtract that number of days from your date you get the first day of the month: TIMESTAMPADD(SQL_TSI_DAY, 1 - DAYOFMONTH(CURRENT_DATE), CURRENT_DATE)

If now you use this in the formula of the previous post you get the first day of the same month 1 year ago.

When you have that date to have the last day of the same month (current month 1 year ago) you must only add 1 month and subtract 1 day.

All you need is TIMESTAMPADD, DAYOFMONTH, CURRENT_DATE and nest them all together as many times as needed to get what you require.