datediff.sql

主要 用于通知故障现场通过短信的方式对技术员工提供处理故障的信息

CREATE OR REPLACE FUNCTION datediff
(form varchar2,date1 date,date2 date)
return number
as
 t_Form		VARCHAR2(10);
begin
	t_Form :=LOWER(RTRIM(LTRIM(form)));
    if t_Form='year'
    then
	    return trunc(to_char(date2,'YYYY')-to_char(date1,'YYYY'));
    elsif t_Form='month'
    then
    	return 	(trunc(to_char(date2,'YYYY'))-trunc(to_char(date1,'YYYY')))*12+
       			(trunc(to_char(date2,'MM'))  -trunc(to_char(date1,'MM')));
    elsif t_Form='day'
    then
    	return (trunc(date2)-trunc(date1));
    elsif t_Form='hour'
    then
    	return trunc((date2-date1)*24);
    elsif t_Form='minute'
    then
    	return trunc((date2-date1)*24*60);
    elsif t_Form='second'
    then
    	return trunc((date2-date1)*24*60*60);
    end if;
end;
/
show errors;