create or replace function CHECK_MONTHEND(IN_DATE VARCHAR2)
RETURN INTEGER
AS
YER VARCHAR2(4);
MON VARCHAR2(2);
TOD VARCHAR2(2);
BEGIN
IF INSTR(IN_DATE, '-', 1, 1)=0 AND LENGTH(IN_DATE)=8 THEN
YER :=SUBSTR(IN_DATE,1,4);
MON :=SUBSTR(IN_DATE,5,2);
TOD :=SUBSTR(IN_DATE,7,2);
ELSE
IF INSTR(IN_DATE, '-', 1, 1)=5 AND LENGTH(IN_DATE)=10 THEN
YER :=SUBSTR(IN_DATE,1,4);
MON :=SUBSTR(IN_DATE,6,2);
TOD :=SUBSTR(IN_DATE,9,2);
ELSE
RETURN 0;
END IF;
END IF;
IF MON NOT IN ('01','02','03','04','05','06','07','08','09','10','11','12') THEN
RETURN 0;
END IF;
IF MON IN ('01','03','05','07','08','10','12') AND (TOD!='31') THEN
RETURN 0;
END IF;
IF MON IN('04','06','09','11') AND (TOD!='30') THEN
RETURN 0;
END IF;
IF MON='02' AND ((MOD(YER,4)=0 AND MOD(YER,100)<>0 OR MOD(YER,400)=0)) AND (TOD!='29') THEN
RETURN 0;
END IF;
IF MON='02' AND ((MOD(YER,4)!=0 AND MOD(YER,100)<>0 and MOD(YER,400)!=0)) AND ( TOD!='28') THEN
RETURN 0;
END IF;
RETURN 1;
end CHECK_MONTHEND;
---------------------------------------------------------------------
统计类,上月月底
DB2中: 可根据当前日期求出上月月底日期 to_char(current date - day(current date) day,'yyyymmdd')
RETURN INTEGER
AS
YER VARCHAR2(4);
MON VARCHAR2(2);
TOD VARCHAR2(2);
BEGIN
IF INSTR(IN_DATE, '-', 1, 1)=0 AND LENGTH(IN_DATE)=8 THEN
YER :=SUBSTR(IN_DATE,1,4);
MON :=SUBSTR(IN_DATE,5,2);
TOD :=SUBSTR(IN_DATE,7,2);
ELSE
IF INSTR(IN_DATE, '-', 1, 1)=5 AND LENGTH(IN_DATE)=10 THEN
YER :=SUBSTR(IN_DATE,1,4);
MON :=SUBSTR(IN_DATE,6,2);
TOD :=SUBSTR(IN_DATE,9,2);
ELSE
RETURN 0;
END IF;
END IF;
IF MON NOT IN ('01','02','03','04','05','06','07','08','09','10','11','12') THEN
RETURN 0;
END IF;
IF MON IN ('01','03','05','07','08','10','12') AND (TOD!='31') THEN
RETURN 0;
END IF;
IF MON IN('04','06','09','11') AND (TOD!='30') THEN
RETURN 0;
END IF;
IF MON='02' AND ((MOD(YER,4)=0 AND MOD(YER,100)<>0 OR MOD(YER,400)=0)) AND (TOD!='29') THEN
RETURN 0;
END IF;
IF MON='02' AND ((MOD(YER,4)!=0 AND MOD(YER,100)<>0 and MOD(YER,400)!=0)) AND ( TOD!='28') THEN
RETURN 0;
END IF;
RETURN 1;
end CHECK_MONTHEND;
---------------------------------------------------------------------
统计类,上月月底
DB2中: 可根据当前日期求出上月月底日期 to_char(current date - day(current date) day,'yyyymmdd')