MONTHS_BETWEEN(DATE1, DATE2) Used to calculate the difference in months between two dates.
I recently received a migration requirement to putOracle SQLinterface is migrated to the new platform, but the new platform is built using theJavaThe way the calculations are done, so I need to convert the SQL logic to Java language.
run intoMONTHS_BETWEENWhen I did, I encountered some strange problems, which I'll document here.
recreate a scene
At first, my general idea: first calculate the month difference between the two dates, then take the start date plus the month difference and then calculate the day difference with the end date, if the day difference is greater than 0, the month difference +1; day difference is less than 0, the month difference -1.
Why don't you keep decimals?
Because the SQL logic used to MONTHS_BETWEEN are used to calculate the last x months, x months in the future, such data, only need to determine whether greater than or less than a certain integer, all here to take the whole number is no problem (at the time it was thought).
package ;
import .slf4j.Slf4j;
import .;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
@Slf4j
public class DateUtil {
public static final SimpleDateFormat yyyyMMddDateFormat = new SimpleDateFormat("yyyyMMdd");
public static Date strToDate(String str) {
if ((str)) {
return null;
}
return (str, new ParsePosition(0));
}
/**
* Calculate the difference between two dates and months
*
* @param begDate Start date
* @param endDate End date
* @return difference in months
*/
public static Integer monthsBetween(Date begDate, Date endDate) {
try {
if ((begDate) || (endDate)) {
return null;
}
Temporal beg = ().atZone(()).toLocalDate();
Temporal end = ().atZone(()).toLocalDate();
int between = (int) (beg, end);
Calendar calendar = ();
(begDate);
(, between);
Date begDateNew = ();
Temporal begNew = ().atZone(()).toLocalDate();
long dayDiff = (begNew, end);
if (dayDiff > 0) {
between += 1;
} else if (dayDiff < 0) {
between -= 1;
}
return between;
} catch (Exception e) {
("DateUtil monthsBetweenWithMon() Occurred Exception.", e);
return null;
}
}
public static void main(String[] args) {
("%-9s %-9s %-3s\n", "dates1", "dates2", "difference in months");
String date1 = "20240405", date2 = "20240807";
Integer between = monthsBetween(strToDate(date1), strToDate(date2));
("%-10s %-10s %-3s\n", date1, date2, between);
}
}
Results against Oracle
| Start date | Closing date | JAVA | ORACLE |
|---|---|---|---|
| 20240405 | 20240807 | 5 | 4.06451612903226 |
| 20240715 | 20240102 | -7 | -6.41935483870968 |
| 20231130 | 20240131 | 3 | 2 |
| 20240117 | 20231224 | -1 | -0.774193548387097 |
| 20240229 | 20240529 | -3 | -3 |
| 20240229 | 20240530 | -4 | -3.03225806451613 |
| 20240229 | 20240531 | -4 | -3 |
| 20240731 | 20240430 | -3 | 3 |
Analysis of results
The self-test and smoke test didn't reveal any problems, and when formally tested, it was found that when both dates were the end of the month, it resulted in incorrect results (20231130 vs. 20240131 in the results).
And it was also found that Orcale'sMONTHS_BETWEENIt's even more out-of-the-box thinking when dealing with the end of the month! For example20240731The last 3 months should be from20240501started the calculation; there is also the case when one of the two dates is the end of February, and when comparing the 29th, 30th, and 31st with the larger month, the month difference between the 29th and 31st is actually the same.
Looked up a lot of information and ended up inORACLE Date Functions MONTHS_BETWEENFind out why in the article.
The MONTHS_BETWEEN function returns the number of months between two dates. If the number of days in the month of the two dates are the same, or are the last day of a month, return an integer, otherwise, the return value with a decimal to 1/31 months per day to calculate the number of days remaining in the month. If date 1 is smaller than date 2 , the return value is negative.
Problem solving
Thoughts:
Day Difference = 0 if both dates are end of month, otherwise (Start Date Day - End Date Day)
Monthly difference = (Year of start date * 12 + Month of start date) - (Year of end date * 12 + Month of end date) + (Day difference / 31)
package ;
import .slf4j.Slf4j;
import .;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
@Slf4j
public class DateUtil {
public static final SimpleDateFormat yyyyMMddDateFormat = new SimpleDateFormat("yyyyMMdd");
public static Date strToDate(String str) {
if ((str)) {
return null;
}
return (str, new ParsePosition(0));
}
/**
* Determine if the date is the end of the month
* @param date dates
* @return Is month-end
*/
public static Boolean isEndOfMonth(Calendar date) {
if ((date)) {
return false;
}
return (Calendar.DAY_OF_MONTH) == (Calendar.DAY_OF_MONTH);
}
/**
* adapterORACLEcomprehensive databaseMONTHS_BETWEEN()calculation result
* MONTHS_BETWEEN(startDate, endDate)
*
* @param startDate Starting time
* @param endDate Time of results
* @return difference in months
*/
public static BigDecimal oracleMonthsBetween(Date startDate, Date endDate) {
Calendar startCalendar = ();
(startDate);
Calendar endCalendar = ();
(endDate);
int startYear = ();
int endYear = ();
int startMonth = ();
int endMonth = ();
int startDay = ();
int endDay = ();
// difference in months
double result = (startYear * 12 + startMonth) - (endYear * 12 + endMonth);
// decimal month
double countDay;
// 如果是两个dates都是月末，Just deal with the months.；Otherwise use the day difference / 31 算出decimal month
if (isEndOfMonth(startCalendar) && isEndOfMonth(endCalendar)) {
countDay = 0;
} else {
countDay = (startDay - endDay) / 31d;
}
result += countDay;
// Return and retain14decimal places
return (result)
.setScale(14, RoundingMode.HALF_UP)
.stripTrailingZeros();
}
public static void main(String[] args) {
("%-9s %-9s %-3s\n", "dates1", "dates2", "difference in months");
String date1 = "20240405", date2 = "20240807";
BigDecimal between = oracleMonthsBetween(strToDate(date1), strToDate(date2));
("%-10s %-10s %-3s\n", date1, date2, ());
}
}
Comparison of results with Oracle
| Start date | End date | JAVA | ORACLE |
|---|---|---|---|
| 20240405 | 20240807 | -4.06451612903226 | -4.06451612903226 |
| 20240423 | 20240614 | -1.70967741935484 | -1.70967741935484 |
| 20240229 | 20240529 | -3 | -3 |
| 20240229 | 20240530 | -3.03225806451613 | -3.03225806451613 |
| 20240229 | 20240531 | -3 | -3 |
| 20230228 | 20230528 | -3 | -3 |
| 20231130 | 20240131 | -2 | -2 |
| 20231130 | 20240201 | -2.06451612903226 | -2.06451612903226 |
| 20240731 | 20240430 | 3 | 3 |
| 20240731 | 20240429 | 3.06451612903226 | 3.06451612903226 |
| 20240430 | 20240731 | -3 | -3 |
| 20240114 | 20231010 | 3.12903225806452 | 3.12903225806452 |