如何获取两个日期之间的月份数？

Question

SQL>  create table a
  2   (a1 date,
  3    a2 date);表已创建。SQL>  insert into a values ('2006-4-3','2006-2-21');已创建 1 行。SQL>  insert into a values ('2006-4-1','2003-12-2');已创建 1 行。SQL>  insert into a values ('2005-3-31','2002-5-1');已创建 1 行。SQL> select a.*,Months_between(a1,a2) from a;A1         A2         MONTHS_BETWEEN(A1,A2)
---------- ---------- ---------------------
2006-04-03 2006-02-21            1.41935484
2006-04-01 2003-12-02            27.9677419
2005-03-31 2002-05-01            34.9677419SQL> select a.*,Months_between(a1,a2) MB,ceil(Months_between(a1,a2)) MN from a;A1         A2                 MB         MN
---------- ---------- ---------- ----------
2006-04-03 2006-02-21 1.41935484          2
2006-04-01 2003-12-02 27.9677419         28
2005-03-31 2002-05-01 34.9677419         35

Answer 1

嘿嘿,Oracle 还有这样的函数的

Answer 2

select months_between(to_date('20061104','yyyymmdd'),to_date('20060104','yyyymmdd')) from dual
大的日期在前面

Answer 3

SQL> select months_between('19-12月-1999','19-3月-1999') mon_between from dual;MON_BETWEEN
-----------
          9
SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd')) mon_betw from dual; MON_BETW
---------
      -60

Answer 4

sanoul(垃圾) ( )　正解了

Answer 5

想不到Oracle中还有这样的函数，受教了

Answer 6

select ceil(months_between('20040401','20040229')) from dual

Answer 7

select ceil(months_between('20040401','20040229')) from dual

Answer 8

呵呵，写的完整一点：
select Ceil(months_between(to_date('20040401','yyyymmdd'),to_date('20040229','yyyymmdd'))) from dual;
不用to_date可能会出现“文字与格式字符串不匹配”的错误