Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Calculate month between 2 dates

  1. #1
    Join Date
    Nov 2015
    Posts
    2

    Question 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. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    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.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Feb 2014
    Posts
    5

    Default

    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. #4
    Join Date
    Nov 2015
    Posts
    2

    Default

    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_cal = dateAdd(enddate, "m", month_diff*-1);
    day_diff = dateDiff(startdate,day_cal, "d");
    } else {
    day_cal = dateAdd(startdate, "m", month_diff);
    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
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.