计算时间差考虑年月日一块 用oracle计算出员工被雇佣了多少年、多少月、多少日,不是单独计算年、月、日而是放在一块 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 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哈 看看这个?? -- 当然是可以滴!请参考: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 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行。 -- 验证(例如:上面第一条记录):scott@TBWORA> select add_months(to_date('1980-12-17 00:00:00','yyyy-mm-dd hh24:mi:ss'),30*12+7)+25 from dual;ADD_MONTHS(TO_DATE(-------------------2011-08-11 00:00:00-- 正好等于今天的日期!30*12+7)+25:解释:(30年*12个月) + 7个月 + 25天 No suitable driver found 面试题——礼品交换,求sql 存储过程的问题,急!!!!!!!! 这样的条件应该怎么写 请教oracle的block的问题 Oracle9i 的备份与恢复(exp/imp) 请问ORACLE到底有几个版本 菜鸟问题 oracle经常性断开连接 求大神指导 为什么主机和虚拟机能互相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天