The following expressions can be used:
select add_months(trunc(current_date, 'MM'), -12), trunc(current_date, 'MM')-1 from dual;
here trunc function truncates current date by the granularity of month so the returned date out of current date is the first date of current month.
add_months function is used to subract 12 months out of that date. Using interval was not recommended for this purpose according to asktom:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1157035034361
This code bit retrieves the DAY of the first month (sunday or monday, etc.)
select to_char( trunc(current_date, 'MM'), 'DAY' ) from dual;
This link gave a good overview of all available oracle sql functions:
http://www.ss64.com/orasyntax/functions.html
Using these expressions in OBIEE
I was trying to use these in OBIEE Answers report. But had no luck. Still has to figure that out.
If i used these i get the following OBIEE error:
Error getting drill information:
Error Codes: YQCO4T56:OPR4ONWY:U9IM8TAC:OI2DL65P
Odbc driver returned an error (SQLExecDirectW).
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 27002] Near <(>: Syntax error [nQSError: 26012] . (HY000)
