--用函数解决吧 SQL> create or replace function f_time(dt1 date,dt2 date) return varchar2 2 as 3 v_dt varchar2(20); 4 v_h number; 5 v_mi number; 6 v_ss number; 7 begin 8 select round(abs((dt1-dt2)*24)) into v_h from dual; 9 select round(mod(abs((dt1-dt2)*24*60*60),60)) into v_ss from dual; 10 select round(mod(abs((dt1-dt2)*24*60*60),3600)/60) into v_mi from dual; 11 v_dt:=to_char(v_h)||':'||to_char(v_mi,'00')||':'||to_char(v_ss,'00'); 12 return v_dt; 13 end; 14 /函数已创建。SQL> SQL> declare 2 dt1 date; 3 dt2 date; 4 v_dt varchar2(20); 5 begin 6 dt1:=to_date('01/10/2009 08:43:15','dd/mm/yyyy hh24:mi:ss'); 7 dt2:=to_date('30/09/2009 06:40:10','dd/mm/yyyy hh24:mi:ss'); 8 v_dt:=f_time(dt1,dt2); 9 dbms_output.put_line(v_dt); 10 end; 11 / 26: 03: 05PL/SQL 过程已成功完成。
create or replace function time_between_fun(i_todate date, i_fromdate date) return varchar2 is v_hours number(18,0); v_minutes number(18,0); v_seconds number(18,0); v_seconds2 number(18,0); o_time varchar2(20); begin if i_todate >= i_fromDate then select to_number(i_todate-i_fromdate)*24*60*60 into v_seconds from dual; select mod(v_seconds,60) into v_seconds2 from dual; select mod(v_seconds/60,60) into v_minutes from dual; select trunc(i_todate-i_fromDate)*24+mod(v_minutes/60,24) into v_hours from dual; else select to_number(i_fromdate-i_todate)*24*60*60 into v_seconds from dual; select mod(v_seconds,60) into v_seconds2 from dual; select mod(v_seconds/60,60) into v_minutes from dual; select trunc(i_fromdate-i_toDate)*24+mod(v_minutes/60,24) into v_hours from dual; end if; o_time := to_char(v_hours)||':'||to_char(v_minutes)||':'||to_char(v_seconds2); return o_time; end; /scott@SZTYORA> select time_between_fun(to_date('2009-09-10 23:23:23','YYYY-MM-DD HH24:MI:SS'),to_date('2009-09-08','YYYY-MM-DD')) as time_between FROM dual;TIME_BETWEEN ------------------------------------------------------------------------------------ 48:23:23
-- 修正一下:create or replace function time_between_fun(i_todate date, i_fromdate date) return varchar2 is v_hours number(18,0); v_minutes number(18,0); v_seconds number(18,0); v_seconds2 number(18,0); o_time varchar2(20); begin if i_todate >= i_fromDate then select to_number(i_todate-i_fromdate)*24*60*60 into v_seconds from dual; select mod(v_seconds,60) into v_seconds2 from dual; select mod(v_seconds/60,60) into v_minutes from dual; select trunc((i_todate-i_fromDate)*24)+mod(v_minutes/60,24) into v_hours from dual; else select to_number(i_fromdate-i_todate)*24*60*60 into v_seconds from dual; select mod(v_seconds,60) into v_seconds2 from dual; select mod(v_seconds/60,60) into v_minutes from dual; select trunc((i_fromdate-i_toDate)*24)+mod(v_minutes/60,24) into v_hours from dual; end if; o_time := to_char(v_hours)||':'||to_char(v_minutes)||':'||to_char(v_seconds2); return o_time; end; /scott@SZTYORA> select time_between_fun(sysdate,trunc(sysdate)) as time_between from dual;TIME_BETWEEN ------------------------------------------------------------------------------------ 14:13:32
--如果是10g或者是以上版本 简单的办法 select numtoyminterval(months_between(dt2,dt1),'month') year_months, substr(numtodsinterval(dt2-add_months(dt1,trunc(months_between(dt2,dt1))),'day'),9,11) days_hours from (select to_date('20110630 17:51:20','yyyymmdd hh24:mi:ss') dt1, to_date('20110501 14:50:20','yyyymmdd hh24:mi:ss') dt2 from dual)
SQL> create or replace function f_time(dt1 date,dt2 date) return varchar2
2 as
3 v_dt varchar2(20);
4 v_h number;
5 v_mi number;
6 v_ss number;
7 begin
8 select round(abs((dt1-dt2)*24)) into v_h from dual;
9 select round(mod(abs((dt1-dt2)*24*60*60),60)) into v_ss from dual;
10 select round(mod(abs((dt1-dt2)*24*60*60),3600)/60) into v_mi from dual;
11 v_dt:=to_char(v_h)||':'||to_char(v_mi,'00')||':'||to_char(v_ss,'00');
12 return v_dt;
13 end;
14 /函数已创建。SQL>
SQL> declare
2 dt1 date;
3 dt2 date;
4 v_dt varchar2(20);
5 begin
6 dt1:=to_date('01/10/2009 08:43:15','dd/mm/yyyy hh24:mi:ss');
7 dt2:=to_date('30/09/2009 06:40:10','dd/mm/yyyy hh24:mi:ss');
8 v_dt:=f_time(dt1,dt2);
9 dbms_output.put_line(v_dt);
10 end;
11 /
26: 03: 05PL/SQL 过程已成功完成。
create or replace function time_between_fun(i_todate date, i_fromdate date)
return varchar2
is
v_hours number(18,0);
v_minutes number(18,0);
v_seconds number(18,0);
v_seconds2 number(18,0);
o_time varchar2(20);
begin
if i_todate >= i_fromDate then
select to_number(i_todate-i_fromdate)*24*60*60 into v_seconds from dual;
select mod(v_seconds,60) into v_seconds2 from dual;
select mod(v_seconds/60,60) into v_minutes from dual;
select trunc(i_todate-i_fromDate)*24+mod(v_minutes/60,24) into v_hours from dual;
else
select to_number(i_fromdate-i_todate)*24*60*60 into v_seconds from dual;
select mod(v_seconds,60) into v_seconds2 from dual;
select mod(v_seconds/60,60) into v_minutes from dual;
select trunc(i_fromdate-i_toDate)*24+mod(v_minutes/60,24) into v_hours from dual;
end if;
o_time := to_char(v_hours)||':'||to_char(v_minutes)||':'||to_char(v_seconds2);
return o_time;
end;
/scott@SZTYORA> select time_between_fun(to_date('2009-09-10 23:23:23','YYYY-MM-DD HH24:MI:SS'),to_date('2009-09-08','YYYY-MM-DD')) as time_between FROM dual;TIME_BETWEEN
------------------------------------------------------------------------------------
48:23:23
return varchar2
is
v_hours number(18,0);
v_minutes number(18,0);
v_seconds number(18,0);
v_seconds2 number(18,0);
o_time varchar2(20);
begin
if i_todate >= i_fromDate then
select to_number(i_todate-i_fromdate)*24*60*60 into v_seconds from dual;
select mod(v_seconds,60) into v_seconds2 from dual;
select mod(v_seconds/60,60) into v_minutes from dual;
select trunc((i_todate-i_fromDate)*24)+mod(v_minutes/60,24) into v_hours from dual;
else
select to_number(i_fromdate-i_todate)*24*60*60 into v_seconds from dual;
select mod(v_seconds,60) into v_seconds2 from dual;
select mod(v_seconds/60,60) into v_minutes from dual;
select trunc((i_fromdate-i_toDate)*24)+mod(v_minutes/60,24) into v_hours from dual;
end if;
o_time := to_char(v_hours)||':'||to_char(v_minutes)||':'||to_char(v_seconds2);
return o_time;
end;
/scott@SZTYORA> select time_between_fun(sysdate,trunc(sysdate)) as time_between from dual;TIME_BETWEEN
------------------------------------------------------------------------------------
14:13:32
--如果是10g或者是以上版本 简单的办法
select numtoyminterval(months_between(dt2,dt1),'month') year_months,
substr(numtodsinterval(dt2-add_months(dt1,trunc(months_between(dt2,dt1))),'day'),9,11) days_hours
from (select to_date('20110630 17:51:20','yyyymmdd hh24:mi:ss') dt1,
to_date('20110501 14:50:20','yyyymmdd hh24:mi:ss') dt2
from dual)