Oracle函数MONTHS_BETWEEN的作用以及java翻译

Dear 丶 2022-02-24 06:23 127阅读 0赞

使用格式：**MONTHS\_BETWEEN(DATE1,DATE2) 即MONTHS\_BETWEEN(日期1，日期2)**

场景应用：计算两个时间相差的月份差

下面给一些sql以及相应的执行结果：

select months_between(to_date('20100529', 'yyyymmdd'), to_date('20100228', 'yyyymmdd')) as months from dual
     union all
      select months_between(to_date('20100531', 'yyyymmdd'), to_date('20100228', 'yyyymmdd')) as months from dual
      union all
    select months_between(to_date('20100528', 'yyyymmdd'), to_date('20100228', 'yyyymmdd')) as months from dual;

3.03225806451613
    3
    3

这完全不符合常规思维的理解，查阅了资料大致的理解了下：

**函数返回两个日期之间的月份数。如果两个日期月份内天数相同，或者都是某个月的最后一天，返回一个整数，否则，返回数值带小数，以每天1/31月来计算月中剩余天数。如果日期1比日期2小 ，返回值为负数。---呵呵呵呵~~~**

**那就是说只要两个月份的总天数存在差异，就会出现月份差先变大后变小的情况。这绝对是这个函数的逻辑问题！！！**

但是没办法，前辈们偷懒直接用函数计算的，导致现在必须遵循这种逻辑。

下面提供java逻辑（供参考）：

import java.text.ParseException;
    import java.text.SimpleDateFormat;
    import java.util.Calendar;
    import java.util.Date;
    
    public class Test {
    
    	public static void main(String[] args) {
    		// Integer a = 10;
    		// BigDecimal b = new BigDecimal(10.1);
    		// BigDecimal c = new BigDecimal(a + 0.1);
    		//
    		// System.out.println("a:" + a + "///" + "b:" + b + "///" + "c:" + c);
    		// System.out.println(b.toString());
    
    		String str1 = "20110330000000";// 20110330000000 20110330000000
    		String str2 = "20100130000000";// 20150228215906
    		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
    		try {
    			System.out.println(sdf.parse(str1));
    			System.out.println(sdf.parse(str2));
    			System.out.println(months_Between(sdf.parse(str1), sdf.parse(str2)));
    		} catch (ParseException e) {
    			e.printStackTrace();
    		}
    
    	}
    
    	public static double months_Between(Date oldDate, Date newDate) {
    		Calendar old_date = Calendar.getInstance();
    		Calendar new_date = Calendar.getInstance();
    		old_date.setTime(oldDate);
    		new_date.setTime(newDate);
    		double month;
    
    		int year = old_date.get(Calendar.YEAR) - new_date.get(Calendar.YEAR);
    		// 兼容日期大小与参数位置对结果的影响
    		if (year < 0) {
    			year = -year;
    			month = year * 12 - calMonthAndDay(old_date, new_date);
    		} else {
    			month = year * 12 + calMonthAndDay(old_date, new_date);
    		}
    		// 判断输入的两个日期是不是在同一年
    		// if(oldYear!=newYear){
    		// month = calMonthAndDay(old_date,new_date)+((oldYear-newYear)*12);
    		// }else{
    		// month = calMonthAndDay(old_date,new_date);
    		// }
    		return month;
    	}
    
    	public static double calMonthAndDay(Calendar old_date, Calendar new_date) {
    		double month;
    
    		// 在同一年只用处理月份和天数
    		// 说明两个日期都是在当月的最后一天只用处理月份
    		System.out.println(
    				"old_date.getActualMaximum(Calendar.DAY_OF_MONTH)" + old_date.getActualMaximum(Calendar.DAY_OF_MONTH));
    		System.out.println("old_date.get(Calendar.MONTH)" + old_date.get(Calendar.DAY_OF_MONTH));
    		System.out.println(
    				"new_date.getActualMaximum(Calendar.DAY_OF_MONTH)" + new_date.getActualMaximum(Calendar.DAY_OF_MONTH));
    		System.out.println("new_date.get(Calendar.MONTH)" + new_date.get(Calendar.DAY_OF_MONTH));
    		if (old_date.getActualMaximum(Calendar.DAY_OF_MONTH) == old_date.get(Calendar.DAY_OF_MONTH)
    				&& new_date.getActualMaximum(Calendar.DAY_OF_MONTH) == new_date.get(Calendar.DAY_OF_MONTH)) {
    			month = old_date.get(Calendar.MONTH) - new_date.get(Calendar.MONTH);
    		}
    		// 两个日期同天
    		else if (old_date.get(Calendar.DAY_OF_MONTH) == new_date.get(Calendar.DAY_OF_MONTH)) {
    			month = old_date.get(Calendar.MONTH) - new_date.get(Calendar.MONTH);
    		} else {
    			double day = old_date.get(Calendar.DAY_OF_MONTH) - new_date.get(Calendar.DAY_OF_MONTH);
    			month = old_date.get(Calendar.MONTH) - new_date.get(Calendar.MONTH) + day / 31;
    		}
    		return month;
    	}
    }