informix dateadd

informix 11.5模擬sql server dateadd, 但傳入及傳出皆為國曆日期數字

CREATE FUNCTION ifx_f_dateadd
(in_type varchar(5),in_date integer, in_diff integer )
RETURNING integer ;
DEFINE p_date date;
DEFINE p_day integer;
--轉為西元日期
SELECT (((substr((in_date+19110000)::char(8),1,4))||'-'||
               (substr((in_date+19110000)::char(8),5,2))||'-'||
               (substr((in_date+19110000)::char(8),7,2)))::date)
  INTO p_date
  FROM empty;
--改成以下方式來轉換
--因為上述方式會隨著DB版本或onconfig中日期格式指定而變,可能會有錯誤.

SELECT (TO_DATE((in_date+19110000)::char(8),'%Y%m%d'))
  INTO p_date  FROM empty;

IF UPPER(in_type) IN ('DAY','D') THEN
   SELECT p_date  + in_diff into p_date from empty;
ELSE IF UPPER(in_type) in ('MONTH','M') THEN
   SELECT p_date  + in_diff units MONTH into p_date from empty;
  --改成以下方式,因為1011031 取下月時會有The result of a datetime computation is out of range.的錯誤,
  --以下方法閏年也適用
 SELECT add_months(p_date,in_diff ) into p_date from empty;
ELSE IF UPPER(in_type) IN ('YEAR','Y' ) THEN
   SELECT p_date + in_diff units YEAR  into p_date from empty;
  --以下方法閏年也適用 

SELECT add_months(p_date,in_diff*12) into p_date from empty;

END IF; END IF; END IF;  --這一串END IF; 搞了我一個小時才知道要這樣寫....

  SELECT ((substr(p_date,1,4)::integer)-1911)||
           (substr(p_date,6,2))||(substr(p_date,9,2))
     INTO p_day
     FROM empty;
RETURN p_day;

SELECT TO_CHAR(p_date ,'%Y%m%d')::INTEGER - 19110000
  INTO p_day
  FROM empty;
END FUNCTION; 


測試,

CREATE TABLE empty (col1 INT);
INSERT into empty VALUES(0);

select ifx_f_dateadd('y',1010814,-1)  FROM empty;

回傳1000814

select sr_f_dateadd('m',1011031,1)  FROM empty

回傳1011130

select sr_f_dateadd('y',1010229,-1)  FROM empty

回傳1000228


ps.2012/08/14寫第一版,3個月後,知識長進,大改一番.