10 Replies Latest reply: Jan 10, 2013 3:58 AM by dianap RSS

    retrieve december as previous month in 2013

    dianap
      All,

      I have used this code to retrieve previous and upcoming months last year which was working fine..
      If the date was for example 7 September then a list of the previous months has to be shown, and a list of the upcoming months including the current month.
         select  to_char(to_date(level,'mm'),'FMMonth') month
         from  dual
         connect by level < to_number(to_char(sysdate,'mm'))
      
       
      MONTH
      ---------
      January
      February
      March
      April
      May
      June
      July
      August
       
       select  to_char(add_months(sysdate,level - 1),'FMMonth') month
        from  dual
       connect by to_number(to_char(add_months(sysdate,level - 1),'mm')) != 1
       
       
      MONTH
      ---------
      September
      October
      November
      December
      Now we are in January 2013.
      When I run the code for previous months, there are no rows. This is correct as there are no previous month as yet in 2013.

      I would like to expand the code to show December 2012 as the previous month at the beginning of a year.
      How can I achieve this?

      Thanks,
      Diana
        • 1. Re: retrieve december as previous month in 2013
          887479
          select  to_char(
                      add_months(trunc(sysdate,'mm'),(level-1)*-1),
                      'FMMonth'
                         ) month
          from  dual
          connect by ( 
              level < to_number(to_char(trunc(sysdate,'mm'),'mm'))+1
              or 
              level <= 2
                     )
          order by add_months(trunc(sysdate,'mm'),(level-1)*-1);
          
          MONTH   
          ---------
          December  
          January   
          • 2. Re: retrieve december as previous month in 2013
            852736
            One way of doing it.
             select  to_char(add_months(sysdate ,level - 2),'FMYearMonth') month
               from  dual
            connect by level <  14
            • 3. Re: retrieve december as previous month in 2013
              Peter vd Zwan
              Hi,

              Try this:
              select
                to_char(trunc(add_months(sysdate, -level + 1) ,'month'),'FMMonth')
              
              from
                dual
              
              connect by
                level <= case when to_number(to_char(sysdate,'mm')) = 1 then 2 
                              else to_number(to_char(sysdate,'mm')) end
              
              order by
                add_months(sysdate, -level + 1) 
              Regards,

              Peter
              • 4. Re: retrieve december as previous month in 2013
                dianap
                Hi Peter,

                It is not exactly what I want.

                I'll explain with an example:

                We are now in January 2013.

                I would like to have previous month as December 2012 and upcoming months as January 2013 to December 2013.


                When we are in February 2013, I would like to have previous months as January 2013 and upcoming months as February 2013 to December 2013.
                when we are March 2013, I would like to have previous months as January 2013 to February 2013 and upcoming months as March 2013 to December 2013.

                Thanks,
                Diana
                • 5. Re: retrieve december as previous month in 2013
                  jeneesh
                  select  to_char(
                              add_months(sysdate,(level-1)*-1),
                              'FMMonth-YYYY'
                                 ) month
                  from  dual
                  connect by ( 
                      level < to_number(to_char(sysdate,'mm'))+1
                      or 
                      level <= 2
                             )
                  order by add_months(sysdate,(level-1)*-1);
                  
                  MONTH        
                  --------------
                  December-2012  
                  January-2013 
                  Adding result for Dec-2012
                  select  to_char(
                              add_months(sysdate-30,(level-1)*-1),
                              'FMMonth-YYYY'
                                 ) month
                  from  dual
                  connect by ( 
                      level < to_number(to_char(sysdate-30,'mm'))+1
                      or 
                      level <= 2
                             )
                  order by add_months(sysdate-30,(level-1)*-1);
                  
                  MONTH        
                  --------------
                  January-2012   
                  February-2012  
                  March-2012     
                  April-2012     
                  May-2012       
                  June-2012      
                  July-2012      
                  August-2012    
                  September-2012 
                  October-2012   
                  November-2012  
                  December-2012  
                  Edited by: jeneesh on Jan 8, 2013 7:25 PM
                  • 6. Re: retrieve december as previous month in 2013
                    ascheffer
                    Something like this
                    select to_char( add_months( start_date, level - nvl( nullif( to_char( start_date, 'mm' ), '01' ), 2 ) ),'FMMonth yyyy') month
                    from ( select sysdate start_date from dual )
                    connect by level < to_number(to_char(start_date,'mm'))
                    • 7. Re: retrieve december as previous month in 2013
                      ascheffer
                      Or
                      select to_char( add_months( start_date, - level ),'FMMonth yyyy') month
                      from ( select sysdate start_date from dual )
                      connect by level < to_number(to_char(start_date,'mm'))
                      order by level desc
                      • 8. Re: retrieve december as previous month in 2013
                        dianap
                        Maybe this explanation is better :)


                        When I run code in January 2013, I want previous months

                        PREVIOUS MONTH
                        --------------------------
                        January-2012
                        February-2012
                        March-2012
                        April-2012
                        May-2012
                        June-2012
                        July-2012
                        August-2012
                        September-2012
                        October-2012
                        November-2012
                        December-2012


                        When I run code in February 2013, I want previous months

                        PREVIOUS MONTH
                        --------------------------
                        January 2013



                        When I run code in March 2013, I want previous months

                        PREVIOUS MONTH
                        --------------------------
                        January 2013
                        February 2013


                        When I run code in December 2013, I want previous months

                        PREVIOUS MONTH
                        --------------------------
                        January-2013
                        February-2013
                        March-2013
                        April-2013
                        May-2013
                        June-2013
                        July-2013
                        August-2013
                        September-2013
                        October-2013
                        November-2013



                        Thanks,
                        Diana

                        Edited by: dianap on Jan 8, 2013 8:08 AM
                        • 9. Re: retrieve december as previous month in 2013
                          Peter vd Zwan
                          Hi,

                          Try this:
                          select
                            to_char(trunc(add_months(sysdate, -level) ,'month'),'FMMonth-yyyy') previous_months
                           
                          from
                            dual
                           
                          connect by
                            level <= case when to_number(to_char(sysdate,'mm')) = 1 then 12
                                          else to_number(to_char(sysdate,'mm'))-1 end
                           
                          order by
                            add_months(sysdate, - level)
                          If wew replace sysdate with date '2013-01-08' then result is:
                          select
                            to_char(trunc(add_months(date '2013-01-08', -level) ,'month'),'FMMonth-yyyy') previous_months
                           
                          from
                            dual
                           
                          connect by
                            level <= case when to_number(to_char(date '2013-01-08','mm')) = 1 then 12
                                          else to_number(to_char(date '2013-01-08','mm'))-1 end
                           
                          order by
                            add_months(date '2013-01-08', - level)
                          ;
                          PREVIOUS_MONTHS
                          ---------------
                          January-2012    
                          February-2012   
                          March-2012      
                          April-2012      
                          May-2012        
                          June-2012       
                          July-2012       
                          August-2012     
                          September-2012  
                          October-2012    
                          November-2012   
                          December-2012   
                          
                           12 rows selected 
                          If we replace sysdate with date '2013-02-08 then we get:
                          select
                            to_char(trunc(add_months(date '2013-02-08', -level) ,'month'),'FMMonth-yyyy') previous_months
                           
                          from
                            dual
                           
                          connect by
                            level <= case when to_number(to_char(date '2013-02-08','mm')) = 1 then 12
                                          else to_number(to_char(date '2013-02-08','mm'))-1 end
                           
                          order by
                            add_months(date '2013-02-08', - level)
                          ;
                          
                          PREVIOUS_MONTHS
                          ---------------
                          January-2013    
                          Same for 2013--05-08'
                          select
                            to_char(trunc(add_months(date '2013-05-08', -level) ,'month'),'FMMonth-yyyy') previous_months
                           
                          from
                            dual
                           
                          connect by
                            level <= case when to_number(to_char(date '2013-05-08','mm')) = 1 then 12
                                          else to_number(to_char(date '2013-05-08','mm'))-1 end
                           
                          order by
                            add_months(date '2013-05-08', - level)
                          ;
                          
                          PREVIOUS_MONTHS
                          ---------------
                          January-2013    
                          February-2013   
                          March-2013      
                          April-2013      
                          Regards,

                          Peter