用oracle计算出员工被雇佣了多少年、多少月、多少日,不是单独计算年、月、日而是放在一块
解决方案 »
- oracle database.zip不能解压
- 等大神帮帮忙
- PLSQL Developer写存储过程时提示名字太长,有32位
- oracle动态sql中使用位操作(bitand/bitor)的问题
- ORACLE - Can not allocate log, archival required
- 如果有十几二十个参数要传入存储过程处理,有没有什么简洁的方法?
- C#和Oracle
- 如何在window 2000下用oracle9i手工创建数据库(不用助手)
- 一个菜鸟问题
- 请教一个不怎么难的问题!
- 为什么主机和虚拟机能互相ping通,虚拟机能访问主机,但是主机不能访问虚拟机呢
- oracle 9i 数据库不能打开
select to_char(round(sysdate - to_date('2011-01-01','yyyy-mm-dd') + to_date('1000-01-01','yyyy-mm-dd')),'YY-mm-dd')
from dual
哈 看看这个??
2 sysdate as curr_date,
3 hiredate,
4 months_between(sysdate,hiredate) as all_months,
5 trunc(months_between(sysdate,hiredate)/12) as hire_years,
6 trunc(mod(months_between(sysdate,hiredate)/12,12)) as hire_months,
7 trunc(sysdate-add_months(hiredate,trunc(months_between(sysdate,hiredate)))) as hire_days
8 from emp; EMPNO ENAME CURR_DATE HIREDATE ALL_MONTHS HIRE_YEARS HIRE_MONTHS HIRE_DAYS
---------- -------------------- ------------------- ------------------- ---------- ---------- ----------- ----------
7369 SMITH 2011-08-11 16:22:22 1980-12-17 00:00:00 367.828458 30 6 25
7499 ALLEN 2011-08-11 16:22:22 1981-02-20 00:00:00 365.731684 30 6 22
7521 WARD 2011-08-11 16:22:22 1981-02-22 00:00:00 365.667168 30 6 20
7566 JONES 2011-08-11 16:22:22 1981-04-02 00:00:00 364.312329 30 6 9
7654 MARTIN 2011-08-11 16:22:22 1981-09-28 00:00:00 358.473619 29 5 14
7698 BLAKE 2011-08-11 16:22:22 1981-05-01 00:00:00 363.344587 30 6 10
7782 CLARK 2011-08-11 16:22:22 1981-06-09 00:00:00 362.086523 30 6 2
7788 SCOTT 2011-08-11 16:22:22 1987-04-19 00:00:00 291.763942 24 0 23
7839 KING 2011-08-11 16:22:22 1981-11-17 00:00:00 356.828458 29 5 25
7844 TURNER 2011-08-11 16:22:22 1981-09-08 00:00:00 359.118781 29 5 3
7876 ADAMS 2011-08-11 16:22:22 1987-05-23 00:00:00 290.63491 24 0 19
7900 JAMES 2011-08-11 16:22:22 1981-12-03 00:00:00 356.280071 29 5 8
7902 FORD 2011-08-11 16:22:22 1981-12-03 00:00:00 356.280071 29 5 8
7934 MILLER 2011-08-11 16:22:22 1982-01-23 00:00:00 354.63491 29 5 19已选择14行。
scott@TBWORA> select empno, ename,
2 sysdate as curr_date,
3 hiredate,
4 months_between(sysdate,hiredate) as all_months,
5 trunc(months_between(sysdate,hiredate)/12) as hire_years,
6 mod(trunc(months_between(sysdate,hiredate)),12) as hire_months,
7 trunc(sysdate-add_months(hiredate,trunc(months_between(sysdate,hiredate)))) as hire_days
8 from emp; EMPNO ENAME CURR_DATE HIREDATE ALL_MONTHS HIRE_YEARS HIRE_MONTHS HIRE_DAYS
---------- -------------------- ------------------- ------------------- ---------- ---------- ----------- ----------
7369 SMITH 2011-08-11 16:33:20 1980-12-17 00:00:00 367.828704 30 7 25
7499 ALLEN 2011-08-11 16:33:20 1981-02-20 00:00:00 365.73193 30 5 22
7521 WARD 2011-08-11 16:33:20 1981-02-22 00:00:00 365.667413 30 5 20
7566 JONES 2011-08-11 16:33:20 1981-04-02 00:00:00 364.312575 30 4 9
7654 MARTIN 2011-08-11 16:33:20 1981-09-28 00:00:00 358.473865 29 10 14
7698 BLAKE 2011-08-11 16:33:20 1981-05-01 00:00:00 363.344833 30 3 10
7782 CLARK 2011-08-11 16:33:20 1981-06-09 00:00:00 362.086768 30 2 2
7788 SCOTT 2011-08-11 16:33:20 1987-04-19 00:00:00 291.764188 24 3 23
7839 KING 2011-08-11 16:33:20 1981-11-17 00:00:00 356.828704 29 8 25
7844 TURNER 2011-08-11 16:33:20 1981-09-08 00:00:00 359.119026 29 11 3
7876 ADAMS 2011-08-11 16:33:20 1987-05-23 00:00:00 290.635155 24 2 19
7900 JAMES 2011-08-11 16:33:20 1981-12-03 00:00:00 356.280317 29 8 8
7902 FORD 2011-08-11 16:33:20 1981-12-03 00:00:00 356.280317 29 8 8
7934 MILLER 2011-08-11 16:33:20 1982-01-23 00:00:00 354.635155 29 6 19已选择14行。
-------------------
2011-08-11 00:00:00-- 正好等于今天的日期!30*12+7)+25:解释:(30年*12个月) + 7个月 + 25天