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

# Month Difference

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