5 Replies Latest reply: Feb 20, 2013 12:59 PM by Solomon Yakobson RSS

    How to get last 3 months on basis of sysdate in prompt

    user10675696
      Hi,

      I am using Fiscal Month in the prompts and the data in the column looks like below


      201201
      201202
      201203
      201204


      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)

      Rgds,
      Ma
        • 1. Re: How to get last 3 months on basis of sysdate in prompt
          Solomon Yakobson
          between to_char(add_months(trunc(sysdate,'mm'),-3),'YYYYMM') and to_char(add_months(trunc(sysdate,'mm'),-1),'YYYYMM')
          And to test it:
          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> 
          SY.

          Edited by: Solomon Yakobson on Feb 20, 2013 12:53 PM
          • 2. Re: How to get last 3 months on basis of sysdate in prompt
            Frank Kulash
            Hi,

            Here's one way:
            SELECT       TO_CHAR ( ADD_MONTHS (SYSDATE, -LEVEL)
                        , 'YYYYMM'
                        )     AS f_month
            FROM      dual
            CONNECT BY  LEVEL  <= 3
            ORDER BY  -LEVEL
            ;
            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.
            • 3. Re: How to get last 3 months on basis of sysdate in prompt
              Solomon Yakobson
              Actually, there is no need to truncate:
              between to_char(add_months(sysdate,-3),'YYYYMM') and to_char(add_months(sysdate,-1),'YYYYMM')
              select  to_char(add_months(sysdate,-3),'YYYYMM'),
                      to_char(add_months(sysdate,-1),'YYYYMM')
                from  dual
              /
              
              TO_CHA TO_CHA
              ------ ------
              201211 201301
              
              SQL> 
              SY.
              • 4. Re: How to get last 3 months on basis of sysdate in prompt
                user10675696
                Hi Solomon Yakobson      

                I am using Fiscal Month in the prompts and the data in the column looks like below


                201201
                201202
                201203
                201204


                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

                2012Jan
                2012Feb
                2012Mar
                2012Apr

                Edited by: user10675696 on Feb 20, 2013 10:49 AM
                • 5. Re: How to get last 3 months on basis of sysdate in prompt
                  Solomon Yakobson
                  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:
                  to_char(to_date(your-valuse,'yyyymm'),'yyyyMon')
                  For example:
                  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> 
                  SY.