2 Replies Latest reply on Sep 30, 2010 1:43 PM by riedelme

    Month Difference

    VSat
      Dear All

      I want to find the month difference between two dates ,i tried but iam getting the result like jan -01,jan-02 that is if my date is below 15 then iam getting value as 1 if the date is greater than 15 then i am getting the value as 2. I am attaching the query for your reference please help me out as my entire summary is coming wrong. For 1 month i am getting two answers.

      SELECT TO_CHAR(DEL_DATE,'MON/YYYY') PERIOD,round(months_between(del_date,sysdate)) DIFFMON,
      SUM(CATEGORY) TOPS,SUM(CATEGORY1) BOTTOM,
      SUM(NVL(CATEGORY,0)+NVL(CATEGORY1,0)) TOTAL,DECODE(round(months_between(del_date,sysdate)),0,'100%',1,'100%',
      2,'90%',3,'80%',4,'70%',5,'60%',6,'50%',7,'30%',8,'20%',9,'10%',10,'10%',11,'10%',12,'10%') PERC
      FROM ORDER_CONFIRMATION ,ORDER_PROJECTION
      WHERE TO_CHAR(DEL_DATE,'MON/YYYY')=TO_CHAR(PROJ_MTH_YR (+),'MON/YYYY') AND
      TO_NUMBER(TO_CHAR(LAST_DAY(DEL_DATE),'MM')) - TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE),'MM')) IS NOT NULL
      GROUP BY TO_CHAR(DEL_DATE,'MON/YYYY'),round(months_between(del_date,sysdate))
      ,CAT_TOPS,CAT_BOTTOM
      ORDER BY DIFFMON,PERIOD

      Thanks
      V. Satish
        • 1. Re: Month Difference
          ratina
          Replace <b>ROUND</b> with <b>TRUNC</b>
          SELECT TO_CHAR(DEL_DATE,'MON/YYYY') PERIOD
          , TRUNC(months_between(del_date,sysdate)) DIFFMON
          , SUM(CATEGORY) TOPS
          , SUM(CATEGORY1) BOTTOM
          , SUM(NVL(CATEGORY,0)+NVL(CATEGORY1,0)) TOTAL
          , DECODE(TRUNC(months_between(del_date,sysdate)),0,'100%',1,'100%',2,'90%',3,'80%',4,'70%',5,'60%',6,'50%',7,'30%',8,'20%',9,'10%',10,'10%',11,'10%',12,'10%') PERC
          FROM ORDER_CONFIRMATION ,ORDER_PROJECTION
          WHERE TO_CHAR(DEL_DATE,'MON/YYYY')=TO_CHAR(PROJ_MTH_YR (+),'MON/YYYY') 
          AND TO_NUMBER(TO_CHAR(LAST_DAY(DEL_DATE),'MM')) - TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE),'MM')) IS NOT NULL
          GROUP BY TO_CHAR(DEL_DATE,'MON/YYYY'),
                   TRUNC(months_between(del_date,sysdate)),
                  DECODE(TRUNC(months_between(del_date,sysdate)),0,'100%',1,'100%',2,'90%',3,'80%',4,'70%',5,'60%',6,'50%',7,'30%',8,'20%',9,'10%',10,'10%',11,'10%',12,'10%')
          ORDER BY DIFFMON, PERIOD;
          • 2. Re: Month Difference
            riedelme
            Are you trying to get date values (the date the period end occurs?) or number values (the number of months indicating the difference)?

            If you need a number look up the ADD_MONTHS() function in the documentation.