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

# Month Difference

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