Analog of ORACLE function MONTHS_BETWEEN in Java

Question

Does Java have some analog of Oracle's function MONTHS_BETWEEN?

Answer 1

I've run into the same need and started from @alain.janinm answer which is good but doesn't give the exact same result in some cases.
ex :

Consider months between 17/02/2013 and 11/03/2016 ("dd/MM/yyyy")
Oracle result : 36,8064516129032
Java method from @Alain.janinm answer : 36.74193548387097

Here's the changes i made, to get a closer result to Oracle's months_between() function :

public static double monthsBetween(Date startDate, Date endDate){  

    Calendar cal = Calendar.getInstance(); 

    cal.setTime(startDate);  
    int startDayOfMonth = cal.get(Calendar.DAY_OF_MONTH);
    int startMonth =  cal.get(Calendar.MONTH);
    int startYear = cal.get(Calendar.YEAR);  

    cal.setTime(endDate);
    int endDayOfMonth = cal.get(Calendar.DAY_OF_MONTH);  
    int endMonth =  cal.get(Calendar.MONTH);
    int endYear = cal.get(Calendar.YEAR);  


    int diffMonths = endMonth - startMonth;
    int diffYears = endYear - startYear;
    int diffDays = endDayOfMonth - startDayOfMonth;

    return (diffYears * 12) + diffMonths + diffDays/31.0;
} 

With this function the result of the call for the dates 17/02/2013 and 11/03/2016 is : 36.806451612903224

Note : From my understanding Oracle's months_between() function considers that all months are 31 days long

Answer 2

You can do that with :

public static int monthsBetween(Date minuend, Date subtrahend){  

    Calendar cal = Calendar.getInstance();   
    cal.setTime(minuend);  
    int minuendMonth =  cal.get(Calendar.MONTH);  
    int minuendYear = cal.get(Calendar.YEAR);  
    cal.setTime(subtrahend);  
    int subtrahendMonth =  cal.get(Calendar.MONTH);  
    int subtrahendYear = cal.get(Calendar.YEAR);  

    return ((minuendYear - subtrahendYear) * (cal.getMaximum(Calendar.MONTH)+1)) +    
    (minuendMonth - subtrahendMonth);  
}  

---

Edit :

According to this documentation MONTHS_BETWEEN return a fractional result, I think this method do the same :

public static void main(String[] args) throws ParseException {
    SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
    Date d = sdf.parse("02/02/1995");
    Date d2 = sdf.parse("01/01/1995");
    System.out.println(monthsBetween(d, d2));

}

public static double monthsBetween(Date baseDate, Date dateToSubstract){  

    Calendar cal = Calendar.getInstance();   
    cal.setTime(baseDate);
    int baseDayOfYear = cal.get(Calendar.DAY_OF_YEAR);  
    int baseMonth =  cal.get(Calendar.MONTH);  
    int baseYear = cal.get(Calendar.YEAR);  

    cal.setTime(dateToSubstract);  
    int subDayOfYear = cal.get(Calendar.DAY_OF_YEAR);
    int subMonth =  cal.get(Calendar.MONTH);  
    int subYear = cal.get(Calendar.YEAR);  

    //int fullMonth = ((baseYear - subYear) * (cal.getMaximum(Calendar.MONTH)+1)) +    
    //(baseMonth - subMonth);  
    //System.out.println(fullMonth);

    return ((baseYear - subYear) * (cal.getMaximum(Calendar.MONTH)+1)) +   
           (baseDayOfYear-subDayOfYear)/31.0;
} 

Answer 3

I had to migrate some Oracle code to java and haven't found the analog for months_between oracle function. While testing listed examples found some cases when they produce wrong results.

So, created my own function. Created 1600+ tests comparing results of db vs my function, including dates with time component - all work fine.

Hope, this can help someone.

public static double oracle_months_between(Timestamp endDate,Timestamp startDate) {

    //MONTHS_BETWEEN returns number of months between dates date1 and date2.
    // If date1 is later than date2, then the result is positive.
    // If date1 is earlier than date2, then the result is negative.
    // If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer.
    // Otherwise Oracle Database calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2.

    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    String endDateString = sdf.format(endDate), startDateString = sdf.format(startDate);

    int startDateYear = Integer.parseInt(startDateString.substring(0,4)), startDateMonth = Integer.parseInt(startDateString.substring(5,7)), startDateDay = Integer.parseInt(startDateString.substring(8,10));
    int endDateYear = Integer.parseInt(endDateString.substring(0,4)), endDateMonth = Integer.parseInt(endDateString.substring(5,7)), endDateDay = Integer.parseInt(endDateString.substring(8,10));

    boolean endDateLDM = is_last_day(endDate), startDateLDM = is_last_day(startDate);

    int diffMonths = -startDateYear*12 - startDateMonth + endDateYear * 12 + endDateMonth;

    if (endDateLDM && startDateLDM || extract_day(startDate) == extract_day(endDate)){
        // If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer.
        return (double)(diffMonths);
    }

    double diffDays = (endDateDay - startDateDay)/31.;

    Timestamp dStart = Timestamp.valueOf("1970-01-01 " + startDateString.substring(11)), dEnd = Timestamp.valueOf("1970-01-01 " + endDateString.substring(11));

    return diffMonths + diffDays + (dEnd.getTime()-dStart.getTime())/1000./3600./24./31.;
}

public static boolean is_last_day(Timestamp ts){
    Calendar calendar = Calendar.getInstance();
    calendar.setTime(ts);
    int max = calendar.getActualMaximum(Calendar.DAY_OF_MONTH);
    return max == Integer.parseInt((new SimpleDateFormat("dd").format(ts)));
}