informix datediff

CREATE FUNCTION datediff
(in_type varchar(5),in_sdate integer, in_edate integer )
RETURNING integer ;
DEFINE p_sdate date;
DEFINE p_edate date;
DEFINE p_day integer;

--如果國曆日期有誤,一律傳回NULL
BEGIN
ON EXCEPTION
   LET p_day = null::integer;
END EXCEPTION

--判別是否為民國前
IF in_sdate < 0 THEN
   LET in_sdate = ABS(in_sdate)+19110000- ROUND(ABS(in_sdate/10000))*20000;
ELSE
   LET in_sdate =  in_sdate + 19110000;
END IF ;
IF in_edate < 0 THEN
   LET in_edate = ABS(in_edate)+19110000- ROUND(ABS(in_edate/10000))*20000;
ELSE
   LET in_edate =  in_edate + 19110000;
END IF ;

--轉為西元日期
SELECT (TO_DATE((in_edate)::char(8),'%Y%m%d'))
     , (TO_DATE((in_sdate)::char(8),'%Y%m%d'))
  INTO p_edate,p_sdate
  FROM empty;

IF UPPER(in_type) IN ('DAY','D') THEN
   LET p_day = p_edate - p_sdate;
ELSE IF UPPER(in_type) IN ('MONTH','M') THEN
   LET p_day = ((YEAR(p_edate) - YEAR(p_sdate)) * 12) +
                (MONTH(p_edate) - MONTH(p_sdate));
ELSE IF UPPER(in_type) IN ('YEAR','Y') THEN
   LET p_day = YEAR(p_edate) - YEAR(p_sdate) ;
END IF; END IF; END IF;

END;

RETURN p_day;

END FUNCTION;