This discussion is archived
10 Replies Latest reply: Jan 10, 2013 3:58 AM by dianap RSS

retrieve december as previous month in 2013

dianap Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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
  • 10. Re: retrieve december as previous month in 2013
    dianap Newbie
    Currently Being Moderated
    Thanks Peter.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points