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

ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD

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

Gianni Ceresa

Hi,

TIMESTAMPADD(SQL_TSI_YEAR, -1, CURRENT_DATE)

ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD

Hi,

Thank you sir..

ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD

Hi @Gianni Ceresa,

How to calculate the Last year first day of current month and Last year last day of current month based on current date?

Ex:current date= 2015-JAN-08

Last year first day of current month=2014-JAN-01

Last year last day of current month=2014-JAN-31

sorry for creating new thread.

Please help ,

Thanks in advance,

A.kavya

Gianni Ceresa

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.

Sandeep Andhale

Hi Gianni Ceresa,

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

regards
Sandeep

ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD

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.