最近在自改报表的时候,发现原始的计算工作时长存在一些误差,所以就修改了下。顺便做做笔记,方便以后查阅!
原始sql:
create or replace function f_number_to_time(as_number in number) return varchar2 is
as_Result varchar2(100);
begin
as_Result := nvl(substr((as_number) * 24,
1,
instr(((as_number) * 24), '.') - 1),
'0') || '小时' ||
nvl(round(substr((as_number) * 24,
instr(((as_number) * 24), '.'),
instr(((as_number) * 24), '.') + 2) * 60), '0') || '分钟' ;
return(as_Result);
end ;
修改后:
create or replace function F_TwoDay_Difference_Hour(i_startDay in date,
i_endDay in date)
return varchar2 is
as_Result varchar2(100);
v_houre number;
v_minute number;
v_sec number;
begin
--turnc函数用来取整数;mod函数用来取余;round函数用来四舍五入
--计算小时
select trunc((i_startDay - i_endDay) * 24) into v_houre from dual;
--计算分钟
select trunc(mod(((i_startDay - i_endDay) * 24),1)*60) into v_minute from dual;
--计算秒
select round(mod((i_startDay - i_endDay) * 24*60,1)*60) into v_sec from dual;
as_Result := to_char(v_houre) || '小时' || to_char(v_minute) || '分钟' || to_char(v_sec) || '秒';
return(as_Result);
end;
修改后测试:
根据不同情况测试的数据
select
(select F_TwoDay_Difference_Hour(to_date('2013-12-30 18:10:10', 'yyyy-MM-dd hh24:mi:ss'),
to_date('2013-12-30 08:00:00', 'yyyy-MM-dd hh24:mi:ss'))
from dual),
(select F_TwoDay_Difference_Hour(to_date('2013-12-30 18:59:59', 'yyyy-MM-dd hh24:mi:ss'),
to_date('2013-12-30 08:00:00', 'yyyy-MM-dd hh24:mi:ss'))
from dual),
(select F_TwoDay_Difference_Hour(to_date('2013-12-30 18:59:59', 'yyyy-MM-dd hh24:mi:ss'),
to_date('2013-12-30 08:10:09', 'yyyy-MM-dd hh24:mi:ss'))
from dual),
(select F_TwoDay_Difference_Hour(to_date('2013-12-30 18:59:59', 'yyyy-MM-dd hh24:mi:ss'),
to_date('2013-12-30 08:59:09', 'yyyy-MM-dd hh24:mi:ss'))
from dual),
(select F_TwoDay_Difference_Hour(to_date('2013-12-31 08:10:10', 'yyyy-MM-dd hh24:mi:ss'),
to_date('2013-12-30 18:00:00', 'yyyy-MM-dd hh24:mi:ss'))
from dual),
(select F_TwoDay_Difference_Hour(to_date('2013-12-31 08:44:14', 'yyyy-MM-dd hh24:mi:ss'),
to_date('2013-12-30 18:30:05', 'yyyy-MM-dd hh24:mi:ss'))
from dual),
(select F_TwoDay_Difference_Hour(to_date('2013-12-31 08:44:14', 'yyyy-MM-dd hh24:mi:ss'),
to_date('2013-12-30 18:30:55', 'yyyy-MM-dd hh24:mi:ss'))
from dual),
(select F_TwoDay_Difference_Hour(to_date('2013-12-31 08:44:00', 'yyyy-MM-dd hh24:mi:ss'),
to_date('2013-12-30 18:30:55', 'yyyy-MM-dd hh24:mi:ss'))
from dual)
from dual