4 Replies Latest reply on Sep 25, 2015 9:17 AM by Gianni Ceresa

# OBIEE 11g: How to find the 2nd friday of the month

HI,

How to find the 2nd Friday of the month by using current date in obiee 11g.

Regards,

MIT.

• ###### 1. Re: OBIEE 11g: How to find the 2nd friday of the month

If your backend database is Oracle, your easiest option is to create a function and use the OBIEE EVALUATE function to call your DB function.

All your DB function should be doing is returning the value from a cursor similar to this:

SELECT                 NEXT_DAY (LAST_DAY (ADD_MONTHS (TRUNC (SYSDATE), -1)),

'Friday')

+ 7

second_Friday

FROM DUAL

• ###### 2. Re: OBIEE 11g: How to find the 2nd friday of the month

Hi MIT,

You can try with something like this:

It can still be improved to probably avoid 1-2 transformations but at least it does it.

You will need to check what DAYOFWEEK return in your environment for Friday (in mine Friday = 6) because if it doesn't return 6 for Friday you will need to adjust the formula.

The idea is to find what day is the first day of the month, from that day go to the first Friday (or just stay there if the first day is a Friday). This is done by the 6-DAYOFWEEK( first day of the month ).

And now you only need to add 7 more days, so it's why there is a 7+6-DAYOFWEEK( first day of the month ) to make it easier to read and understand (instead of 13-... ).

• ###### 3. Re: OBIEE 11g: How to find the 2nd friday of the month

Hi, Create Dynamic variable with bellow query use in analytics SELECT NEXT_DAY(NEXT_DAY(TRUNC(SYSDATE, 'MONTH') - 1, 'Friday'), 'Friday') SECOND_FDAY FROM dual;

• ###### 4. Re: OBIEE 11g: How to find the 2nd friday of the month

It looks like the formula skip 1 week when the first day of the month is a Saturday, this one is the correct version: