原文地址:ORACLE中函数MONTHS_BETWEEN的使用
作者:tbqnot19
格式:MONTHS_BETWEEN(DATE1,DATE2)
MONTHS_BETWEEN函数返回两个日期之间的月份数。
SQL> select months_between(to_date('20090228', 'yyyymmdd'), to_date('20080228', 'yyyymmdd')) as months from dual;
MONTHS
----------
12
SQL> select months_between(to_date('20090228', 'yyyymmdd'), to_date('20080229', 'yyyymmdd')) as months from dual;
MONTHS
----------
12
SQL> select months_between(to_date('20080229', 'yyyymmdd'), to_date('20070228', 'yyyymmdd')) as months from dual;
MONTHS
----------
12
SQL> select months_between(to_date('20100331', 'yyyymmdd'), to_date('20100228', 'yyyymmdd')) as months from dual;
MONTHS
----------
1
SQL> select months_between(to_date('20100228', 'yyyymmdd'), to_date('20100128', 'yyyymmdd')) as months from dual;
MONTHS
----------
1
SQL> select months_between(to_date('20100327', 'yyyymmdd'), to_date('20100228', 'yyyymmdd')) as months from dual;
MONTHS
----------
.967741935
SQL> select months_between(to_date('20100330', 'yyyymmdd'), to_date('20100228', 'yyyymmdd')) as months from dual;
MONTHS
----------
1.06451613
SQL> select months_between(to_date('20100228', 'yyyymmdd'), to_date('20100130', 'yyyymmdd')) as months from dual;
MONTHS
----------
.935483871
SQL> select months_between(to_date('20100228', 'yyyymmdd'), to_date('20100131', 'yyyymmdd')) as months from dual;
MONTHS
----------
1
SQL> select months_between(to_date('20100228', 'yyyymmdd'), to_date('20100127', 'yyyymmdd')) as months from dual;
MONTHS
----------
1.03225806
30/31=0.967741935483870967741935 48387097
33/31=1.064516129032258064516129 0322581
29/31=0.935483870967741935483870 96774194
32/31=1.032258064516129032258064 516129
在ORACLE中计算月份的差值,都是月底或是日期相同,都会按整月计算。
如果计算结果不是整数,ORACLE会把31作为分母来计算。
MONTHS_BETWEEN函数返回两个日期之间的月份数。
SQL> select months_between(to_date('20090228', 'yyyymmdd'), to_date('20080228', 'yyyymmdd')) as months from dual;
----------
SQL> select months_between(to_date('20090228', 'yyyymmdd'), to_date('20080229', 'yyyymmdd')) as months from dual;
----------
SQL> select months_between(to_date('20080229', 'yyyymmdd'), to_date('20070228', 'yyyymmdd')) as months from dual;
----------
SQL> select months_between(to_date('20100331', 'yyyymmdd'), to_date('20100228', 'yyyymmdd')) as months from dual;
----------
SQL> select months_between(to_date('20100228', 'yyyymmdd'), to_date('20100128', 'yyyymmdd')) as months from dual;
----------
SQL> select months_between(to_date('20100327', 'yyyymmdd'), to_date('20100228', 'yyyymmdd')) as months from dual;
----------
.967741935
SQL> select months_between(to_date('20100330', 'yyyymmdd'), to_date('20100228', 'yyyymmdd')) as months from dual;
----------
1.06451613
SQL> select months_between(to_date('20100228', 'yyyymmdd'), to_date('20100130', 'yyyymmdd')) as months from dual;
----------
.935483871
SQL> select months_between(to_date('20100228', 'yyyymmdd'), to_date('20100131', 'yyyymmdd')) as months from dual;
----------
SQL> select months_between(to_date('20100228', 'yyyymmdd'), to_date('20100127', 'yyyymmdd')) as months from dual;
----------
1.03225806
30/31=0.967741935483870967741935
33/31=1.064516129032258064516129
29/31=0.935483870967741935483870
32/31=1.032258064516129032258064
在ORACLE中计算月份的差值,都是月底或是日期相同,都会按整月计算。
如果计算结果不是整数,ORACLE会把31作为分母来计算。