oracle 在两者之间函数,在Oracle中,是否有一个函数可以计算两个日期之间的差异?...

问:

在Oracle中,是否有一个函数可以计算两个日期之间的差异?

问:

我们可以“老一套”来延长工期hhh:mi格式使用简单内置函数的组合:

SELECT decode(sign(t.maxst),-1,'-','')||to_char(floor(abs(t.maxst)/60))||

decode(t.maxst,null,'',':')||to_char(mod(abs(t.maxst),60),'FM00')

as MaximumScheduleTime

, decode(sign(t.minst),-1,'-','')||to_char(floor(abs(t.minst)/60))||

decode(t.minst,null,'',':')||to_char(mod(abs(t.minst),60),'FM00')

as MinimumScheduleTime

, decode(sign(t.avgst),-1,'-','')||to_char(floor(abs(t.avgst)/60))

decode(t.avgst,null,'',':')||to_char(mod(abs(t.avgst),60),'FM00')

as AverageScheduleTime

FROM (

SELECT round(max((EndDate - StartDate) *1440),0) as maxst

, round(min((EndDate - StartDate) *1440),0) as minst

, round(avg((EndDate - StartDate) *1440),0) as avgst

FROM table1

) t

select dur as "minutes"

, abs(dur) as "unsigned_minutes"

, floor(abs(dur)/60) as "unsigned_whole_hours"

, to_char(floor(abs(dur)/60)) as "hhhh"

, mod(abs(dur),60) as "unsigned_remainder_minutes"

, to_char(mod(abs(dur),60),'FM00') as "mi"

, decode(sign(dur),-1,'-','') as "leading_sign"

, decode(dur,null,'',':') as "colon_separator"

from (select round(( date_expr1 - date_expr2 )*24*60,0) as dur

from ...

)

(更换

date_expr1

和

date_expr2

带日期表达式)

date_expr1 - date_expr2

round

(或

floor

)将分数分钟分解为整数分钟

abs

函数获取绝对值(将负值更改为正值)

to_char

格式模型

FM00

使用

decode

使用PL/SQL函数可以使SQL语句变得不那么难看

两个日期参数

以(小数)天为单位的持续时间,返回格式为时:英里

(

create function hhhhmi(an_dur in number)

return varchar2 deterministic

is

begin

if an_dur is null then

return null;

end if;

return decode(sign(an_dur),-1,'-','')

|| to_char(floor(abs(an_dur)*24))

||':'||to_char(mod((abs(an_dur)*1440),60),'FM00');

end;

定义函数后:

SELECT hhhhmi(max(EndDate - StartDate)) as MaximumScheduleTime

, hhhhmi(min(EndDate - StartDate)) as MinimumScheduleTime

, hhhhmi(avg(EndDate - StartDate)) as AverageScheduleTime

FROM table1