This content has been marked as final. Show 5 replies
And to test it:
between to_char(add_months(trunc(sysdate,'mm'),-3),'YYYYMM') and to_char(add_months(trunc(sysdate,'mm'),-1),'YYYYMM')
select to_char(add_months(trunc(sysdate,'mm'),-3),'YYYYMM'), to_char(add_months(trunc(sysdate,'mm'),-1),'YYYYMM') from dual / TO_CHA TO_CHA ------ ------ 201211 201301 SQL>
Edited by: Solomon Yakobson on Feb 20, 2013 12:53 PM
Here's one way:
This gets you the last 3 calendar months before, NOT including, the current month. If your fiscal year starts 1 one later than the calendar year, then that's the same as the last 3 fiscal months including the current month.
SELECT TO_CHAR ( ADD_MONTHS (SYSDATE, -LEVEL) , 'YYYYMM' ) AS f_month FROM dual CONNECT BY LEVEL <= 3 ORDER BY -LEVEL ;
Hi Solomon Yakobson
I am using Fiscal Month in the prompts and the data in the column looks like below
User will select the prompts between months period, like below
Between 201201 and 201204.
My requirement is, I need to default this Fiscal month value to last 3 months, Since we are in February, it should look like this.
Between 201211 and 201301 (which is last 3 months) like below
And also at the same time, they want to convert that last two digits of fiscal month like below
Edited by: user10675696 on Feb 20, 2013 10:49 AM
I hope "they want to convert that last two digits of fiscal month like below" just for display purposes, otherwise between will not work since month names do not go in month number order. Anyway, use:
SQL> with t as ( 2 select '201201' yyyymm from dual union all 3 select '201202' from dual union all 4 select '201203' from dual union all 5 select '201204' from dual 6 ) 7 select to_char(to_date(yyyymm,'yyyymm'),'yyyyMon') 8 from t 9 / TO_CHAR ------- 2012Jan 2012Feb 2012Mar 2012Apr SQL>