This discussion is archived
2 Replies Latest reply: Sep 30, 2010 6:43 AM by riedelme RSS

Month Difference

VSat Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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.

Legend

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