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

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)));
}

Answer 3

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 4

Actually, I think the correct implementation is this one:

public static BigDecimal monthsBetween(final Date start, final Date end, final ZoneId zone, final int scale ) {
    final BigDecimal no31 = new BigDecimal(31);

    final LocalDate ldStart = start.toInstant().atZone(zone).toLocalDate();
    final LocalDate ldEnd = end.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();

    final int endDay = ldEnd.getDayOfMonth();
    final int endMonth = ldEnd.getMonthValue();
    final int endYear = ldEnd.getYear();
    final int lastDayOfEndMonth = ldEnd.lengthOfMonth();

    final int startDay = ldStart.getDayOfMonth();
    final int startMonth = ldStart.getMonthValue();
    final int startYear = ldStart.getYear();
    final int lastDayOfStartMonth = ldStart.lengthOfMonth();

    final BigDecimal diffInMonths = new BigDecimal((endYear - startYear)*12+(endMonth-startMonth));
    final BigDecimal fraction;
    if(endDay==startDay || (endDay==lastDayOfEndMonth && startDay==lastDayOfStartMonth)) {
        fraction = BigDecimal.ZERO;
    }
    else {
        fraction = BigDecimal.valueOf(endDay-startDay).divide(no31, scale, BigDecimal.ROUND_HALF_UP);
    }
    return diffInMonths.add(fraction);
}

public static BigDecimal monthsBetween(final Date start, final Date end) {
    return monthsBetween(start, end, ZoneId.systemDefault(), 20);
}

Answer 5

I've the same problem and following the Oracle MONTHS_BETWEEN I have made some changes to @alain.janinm and @Guerneen4 answers in order to correct some cases:

Consider months between 31/07/1998 and 30/09/2013 ("dd/MM/yyyy") Oracle result : 182 Java method from @Guerneen4 answer : 181.96774193548387

The problem is that according to specification if date1 and date2 are both last days of months, then the result is always an integer.

For easy understanding here you can find Oracle MONTHS_BETWEEN specifications: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions089.htm. I copy here to summarize:

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

Here's the changes that I've done get the closest result to the Oracle's months_between() function :

public static double monthsBetween(Date startDate, Date endDate) {
    Calendar calSD = Calendar.getInstance();
    Calendar calED = Calendar.getInstance();

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

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

    int diffMonths = endMonth - startMonth;
    int diffYears = endYear - startYear;
    int diffDays = calSD.getActualMaximum(Calendar.DAY_OF_MONTH) == startDayOfMonth
            && calED.getActualMaximum(Calendar.DAY_OF_MONTH) == endDayOfMonth ? 0 : endDayOfMonth - startDayOfMonth;

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

Answer 6

java.time

The other Answers use the troublesome old Calendar class that is now legacy, supplanted by the java.time classes.

MONTHS_BETWEEN

The doc says:

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.

LocalDate

The LocalDate class represents a date-only value without time-of-day and without time zone.

Retrieve a LocalDate from the database using JDBC 4.2 and later. The java.sql.Date class is now legacy, and can be avoided.

LocalDate start = myResultSet.getObject( … , LocalDate.class ) ;  // Retrieve a `LocalDate` from database using JDBC 4.2 and later.

For our demo here, let’s simulate those retrieved dates.

LocalDate start = LocalDate.of( 2018 , Month.JANUARY , 23 );
LocalDate stop = start.plusDays( 101 );

Period

Calculate the elapsed time as a span of time unattached to the timeline, a Period.

Period p = Period.between( start , stop );

Extract the total number of months.

long months = p.toTotalMonths() ; 

Extract the number of days part, the days remaining after calculating the months.

int days = p.getDays() ;

BigDecimal

For accuracy, use BigDecimal. The double and Double types use floating-point technology, trading away accuracy for fast execution performance.

Convert our values from primitives to BigDecimal.

BigDecimal bdDays = new BigDecimal( days );
BigDecimal bdMaximumDaysInMonth = new BigDecimal( 31 );

Divide to get our fractional month. The MathContext provides a limit to resolving the fractional number, plus a rounding mode to get there. Here we use the constant MathContext.DECIMAL32, because I am guessing the Oracle function is using 32-bit math. The rounding mode is RoundingMode.HALF_EVEN, the default specified by IEEE 754, and also known as “Banker’s rounding” which is more mathematically fair than “schoolhouse rounding” commonly taught to children.

BigDecimal fractionalMonth = bdDays.divide( bdMaximumDaysInMonth , MathContext.DECIMAL32 ); 

Add this fraction to our number of whole months, for a complete result.

BigDecimal bd = new BigDecimal( months ).add( fractionalMonth );

To more closely emulate the behavior of the Oracle function, you may want to convert to a double.

double d = bd.round( MathContext.DECIMAL32 ).doubleValue();

Oracle did not document the gory details of their calculation. So you may need to do some trial-and-error experimentation to see if this code has results in line with your Oracle function.

Dump to console.

System.out.println( "From: " + start + " to: " + stop + " = " + bd + " months, using BigDecimal. As a double: " + d );

See this code run live at IdeOne.com.

From: 2018-01-23 to: 2018-05-04 = 3.3548387 months, using BigDecimal. As a double: 3.354839

Caveat: While I answered the Question as asked, I must remark: Tracking elapsed time as a fraction as seen here is unwise. Instead use the java.time classes Period and Duration. For textual representation, use the standard ISO 8601 format: PnYnMnDTnHnMnS. For example, the Period seen in our example above: P3M11D for three months and eleven days.

---

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

Where to obtain the java.time classes?

• Java SE 8, Java SE 9, and later
  • Built-in.
  • Part of the standard Java API with a bundled implementation.
  • Java 9 adds some minor features and fixes.
• Java SE 6 and Java SE 7
  • Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  • Later versions of Android bundle implementations of the java.time classes.
  • For earlier Android, the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

Answer 7

In Joda Time there is a monthsBetween in the org.joda.time.Months class.

Answer 8

The previous answers are not perfect because they do not handle dates such as Feb 31.

Here is my iterative interpretation of MONTHS_BETWEEN in Javascript...

    // Replica of the Oracle function MONTHS_BETWEEN where it calculates based on 31-day months
    var MONTHS_BETWEEN = function(d1, d2) {
        // Don't even try to calculate if it's the same day
        if (d1.getTicks() === d2.getTicks()) return 0;

        var totalDays = 0;
        var earlyDte = (d1 < d2 ? d1 : d2); // Put the earlier date in here
        var laterDate = (d1 > d2 ? d1 : d2); // Put the later date in here
        // We'll need to compare dates using string manipulation because dates such as 
        // February 31 will not parse correctly with the native date object
        var earlyDteStr = [(earlyDte.getMonth() + 1), earlyDte.getDate(), earlyDte.getFullYear()];

        // Go in day-by-day increments, treating every month as having 31 days
        while (earlyDteStr[2] < laterDate.getFullYear() ||
               earlyDteStr[2] == laterDate.getFullYear() && earlyDteStr[0] < (laterDate.getMonth() + 1) ||
               earlyDteStr[2] == laterDate.getFullYear() && earlyDteStr[0] == (laterDate.getMonth() + 1) && earlyDteStr[1] < laterDate.getDate()) {
            if (earlyDteStr[1] + 1 < 32) {
                earlyDteStr[1] += 1; // Increment the day
            } else {
                // If we got to this clause, then we need to carry over a month
                if (earlyDteStr[0] + 1 < 13) {
                    earlyDteStr[0] += 1; // Increment the month
                } else {
                    // If we got to this clause, then we need to carry over a year
                    earlyDteStr[2] += 1; // Increment the year
                    earlyDteStr[0] = 1; // Reset the month
                }
                earlyDteStr[1] = 1; // Reset the day
            }

            totalDays += 1; // Add to our running sum of days for this iteration
        }
        return (totalDays / 31.0);
    };