Month Difference

dataservices job design
1

Hi All,
Can you please let me know How to get the difference between months in given date range.

Eg:
01-01-2007 to 31-12-2007 = 12 months

Regards,
OSR

osr (BOB member since 2005-07-06)

2

You can find the month differnce either by using SQl or you can make use of Custom function here. In the case of SQl you can write direct function MONTHS_BETWEEN, and if you want to write custom function Then you can write the function as shown below:

$DATE1 = TO_DATE(‘01-JAN-2008’,‘DD-MON-YYYY’);
$DATE2 = TO_DATE(‘22-JAN-2009’,‘DD-MON-YYYY’);
$MM_DIFF = TO_CHAR($DATE2,‘MM’) + (12 - TO_CHAR($DATE1,‘MM’));
$YY_DIFF = TO_CHAR($DATE2,‘YYYY’) - TO_CHAR($DATE1,‘YYYY’)-1;
$FINAL_MM = ($YY_DIFF *12) + $MM_DIFF ;
PRINT($FINAL_MM);
PRINT($MM_DIFF);
PRINT($YY_DIFF);

I think This will solve your problem.

-Sunil Lakhani

Sunil Lakhani (BOB member since 2009-01-19)

3

sorry i missed one statement in the previous coding for custom function i.e. return statement

Please review the code again as shown below

$DATE1 = TO_DATE(‘01-JAN-2008’,‘DD-MON-YYYY’);
$DATE2 = TO_DATE(‘22-JAN-2009’,‘DD-MON-YYYY’);
$MM_DIFF = TO_CHAR($DATE2,‘MM’) + (12 - TO_CHAR($DATE1,‘MM’));
$YY_DIFF = TO_CHAR($DATE2,‘YYYY’) - TO_CHAR($DATE1,‘YYYY’)-1;
$FINAL_MM = ($YY_DIFF *12) + $MM_DIFF ;
Return ($FINAL_MM);

Sunil Lakhani (BOB member since 2009-01-19)

4

In 12.0 the date_diff supports months as well.

date_diff($date1, $date2, ‘MM’)

Werner Daehn :de: (BOB member since 2004-12-17)