6 Replies Latest reply on May 4, 2013 1:38 PM by ranit B

    Month-wise break.


      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 ?

        • 1. Re: Month-wise break.

          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.

            The parameters are user dependent and can vary.

            • 3. Re: Month-wise break.
              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))

              • 4. Re: Month-wise break.
                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

                  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
                  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
                    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?