MONTHS_BETWEEN求两个日期之间相差几个月
格式:MONTHS_BETWEEN(日期1,日期2)
例:求所有员工工作了几个月
itkaifang@SCOTT> SELECT empno,ename,hiredate,MONTHS_BETWEEN(SYSDATE,hiredate) AS new FROM emp; EMPNO ENAME HIREDATE NEW ---------- ---------- ---------- ---------- 7369 SMITH 1980-17-12 434.856268 7499 ALLEN 1981-20-02 432.759494 7521 WARD 1981-22-02 432.694978 7566 JONES 1981-02-04 431.340139 7654 MARTIN 1981-28-09 425.50143 7698 BLAKE 1981-01-05 430.372397 7782 CLARK 1981-09-06 429.114333 7788 SCOTT 1987-19-04 358.791752 7839 KING 1981-17-11 423.856268 7844 TURNER 1981-08-09 426.146591 7876 ADAMS 1987-23-05 357.66272 7900 JAMES 1981-03-12 423.307881 7902 FORD 1981-03-12 423.307881 7934 MILLER 1982-23-01 421.66272 14 rows selected. itkaifang@SCOTT>
上题扩展:去除最后所得月数的小数位
itkaifang@SCOTT> SELECT empno,ename,hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)) AS new FROM emp; EMPNO ENAME HIREDATE NEW ---------- ---------- ---------- ---------- 7369 SMITH 1980-17-12 434 7499 ALLEN 1981-20-02 432 7521 WARD 1981-22-02 432 7566 JONES 1981-02-04 431 7654 MARTIN 1981-28-09 425 7698 BLAKE 1981-01-05 430 7782 CLARK 1981-09-06 429 7788 SCOTT 1987-19-04 358 7839 KING 1981-17-11 423 7844 TURNER 1981-08-09 426 7876 ADAMS 1987-23-05 357 7900 JAMES 1981-03-12 423 7902 FORD 1981-03-12 423 7934 MILLER 1982-23-01 421 14 rows selected. itkaifang@SCOTT>
上题扩展:求所有员工工作了几年
itkaifang@SCOTT> SELECT empno,ename,hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year FROM emp; EMPNO ENAME HIREDATE YEAR ---------- ---------- ---------- ---------- 7369 SMITH 1980-17-12 36 7499 ALLEN 1981-20-02 36 7521 WARD 1981-22-02 36 7566 JONES 1981-02-04 35 7654 MARTIN 1981-28-09 35 7698 BLAKE 1981-01-05 35 7782 CLARK 1981-09-06 35 7788 SCOTT 1987-19-04 29 7839 KING 1981-17-11 35 7844 TURNER 1981-08-09 35 7876 ADAMS 1987-23-05 29 7900 JAMES 1981-03-12 35 7902 FORD 1981-03-12 35 7934 MILLER 1982-23-01 35 14 rows selected. itkaifang@SCOTT>
计算两个时间相差的天数
itkaifang@SCOTT> SELECT (TO_DATE('20170312','yyyy-mm-dd')-TO_DATE('20140401','yyyy-mm-dd '))*decode(upper('mi'),'ss',24*60,'mi',24*60,'hh',24,1) AS val FROM dual; VAL ---------- 1076 itkaifang@SCOTT>