This discussion is archived
6 Replies Latest reply: May 4, 2013 6:38 AM by ranit B RSS

Month-wise break.

997766 Newbie
Currently Being Moderated
Hi,

I have a requirement as follows :-

The user will submit the start date and end date. Based on the date parameters, the customized procedure should do some calculations month-wise.

For eg : start date - 01-Dec-2012 to end date - 31-Mar-2013

I want the break up as
Start date End date
01-dec-2012 31-dec-2012
01-jan-2013 31-jan-2013
01-feb-2013 28-feb-2013
01-mar-2013 31-mar-2013


How can i achieve this month-wise break ?

Regards,
  • 1. Re: Month-wise break.
    user639304 Explorer
    Currently Being Moderated
    Hi,

    Is start date always the first date of a month?

    Is end date always the last date of a month?
  • 2. Re: Month-wise break.
    997766 Newbie
    Currently Being Moderated
    Hi,

    The parameters are user dependent and can vary.

    Regards,
  • 3. Re: Month-wise break.
    Etbin Guru
    Currently Being Moderated
    Maybe NOT TESTED!
    select case level when 1
                      then :start_date
                      else add_months(trunc(:start_date,'mm'),level - 1)
           end date_from,
           case level when ceil(months_between(:end_date,:start_date))
                      then :end_date
                      else last_day(add_months(trunc(:start_date,'mm'),level - 1))
           end date_to
      from dual
    connect by level <= ceil(months_between(:end_date,:start_date))
    Regards

    Etbin
  • 4. Re: Month-wise break.
    997766 Newbie
    Currently Being Moderated
    Getting error as

    ORA-00932: inconsistent datatypes: expected DATE got NUMBER
    00932. 00000 - "inconsistent datatypes: expected %s got %s"
  • 5. Re: Month-wise break.
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    There are no DATE bind variables, at least not in SQL*Plus.

    You can use VARCHAR2s to pass the start and end dates, like this:
    VARIABLE  start_date     VARCHAR2 (11)
    VARIABLE  end_date     VARCHAR2 (11)
    EXEC     :start_date := '02-Dec-2012';
    EXEC      :end_date   := '31-Mar-2013';
    
    
    WITH      got_months   AS
    (
         SELECT  TRUNC ( TO_DATE (:start_date, 'DD-Mon-YYYY')
                    , 'MONTH'
                    )          AS first_month
         ,       TRUNC ( TO_DATE (:end_date,   'DD-Mon-YYYY')
                    , 'MONTH'
                    )          AS last_month
         FROM    dual
    )
    SELECT     ADD_MONTHS (first_month, LEVEL - 1)     AS month_start
    ,     ADD_MONTHS (first_month, LEVEL    ) - 1     AS month_end
    FROM     got_months
    CONNECT BY     LEVEL     <= 1 + MONTHS_BETWEEN ( last_month
                                          , first_month
                                   )
    ;
    Output:
    MONTH_START MONTH_END
    ----------- -----------
    01-Dec-2012 31-Dec-2012
    01-Jan-2013 31-Jan-2013
    01-Feb-2013 28-Feb-2013
    01-Mar-2013 31-Mar-2013
  • 6. Re: Month-wise break.
    ranit B Expert
    Currently Being Moderated
    Something like this?
    ranit@XE11GR2>> with xx as
      2  (
      3             select TO_DATE('01-Dec-2012','dd-Mon-yyyy') sd, TO_DATE('31-Mar-2013','dd-Mon-yyyy') ed from dual
      4  )
      5  select
      6     ADD_MONTHS(sd,level-1) "start_Date",
      7     LAST_DAY(ADD_MONTHS(sd,level-1)) "end_Date"
      8  from xx
      9  connect by level <= CEIL(MONTHS_BETWEEN(ed,sd));
    
    start_Dat end_Date
    --------- ---------
    01-DEC-12 31-DEC-12
    01-JAN-13 31-JAN-13
    01-FEB-13 28-FEB-13
    01-MAR-13 31-MAR-13
    ADD_MONTHS, LAST_DAY, MONTHS_BETWEEN, CEIL - are all Oracle built-in functions. Check docs for more details.

    Function CEIL is used in order to round-off dates to the next higher level in cases where there's a fractional day occurence.
    Ex -
    If the result of End_Date and Start_Date comes to be 3.45, it means 3 Months have passed and some fraction of the 4th month.
    In such cases, we need to consider the 4th month also.

    But what happens if Start_Date is not the first day of a month (say '11-Dec-2012') and another scenario the End_Date is not the last day of the month (say '14-Mar-2013')?
    In such case, what is your expected o/p?

Legend

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