# Thread: Calculate month between 2 dates

1. Junior Member
Join Date
Nov 2015
Posts
2

## Calculate month between 2 dates

Hi Gurus,

I'm trying to calculate month diff between 2 dates. The ideal output should be similar to oracle months_between function and returns a double/decimal value.

e.g.

months_between(2016-12-31, 2016-11-30) = 1.0 # as both are the last date of a month
months_between(2016-12-30, 2016-11-30) = 1.0 # as both are the same number of day in a month
months_between(2016-12-31, 2016-11-20) = 1.35
months_between(2016-12-31, 2016-12-20) = 0.32

Any idea?

TIA

2. Senior Member
Join Date
Jun 2012
Posts
5,534
Is that Oracle function really useful?
AFAIK there is no equivalent function in Kettle.
If you must you can easily implement it using a User-Defined-Java-Class.

3. Junior Member
Join Date
Feb 2014
Posts
5
There is a "Date A - Date B (in days)" calculation in the calculator step that you could use as a basis, and go from there to convert to months.

Alternatively there id the "DATEDIF" fucntion in the formula step. By combining multiple calls, one to determine the months, another to determine the days, you should also be able to calculate the value you require.

4. Junior Member
Join Date
Nov 2015
Posts
2
Thanks guys. Here's what I end with:

var month_diff;
var daynumber_start;
var daynumber_end;
var day_cal;
var day_diff;
var month_between;

month_diff = dateDiff(startdate,enddate,"m");
daynumber_start = getDayNumber(startdate, "m");
daynumber_end = getDayNumber(enddate, "m");

if (daynumber_start < daynumber_end) {
day_diff = dateDiff(startdate,day_cal, "d");
} else {
day_diff = dateDiff(day_cal,enddate, "d");
}

month_between = abs((day_diff/30) + month_diff);

Not pretty, but apparently give me the output I need.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•