日期格式清洗

CREATE OR REPLACE FUNCTION FN_GZK_DATE(V_DATESTR VARCHAR2 --日期入参
) RETURN NUMBER – 返回1为正确，0为错误。
AS
/------------------------------------------------------------------------
公用函数：日期检查函数
调用范例: SELECT FN_ISDATE(‘20140501’) FROM DUAL;
------------------------------------------------------------------------/
V1_DATESTR VARCHAR(2000);
V2_DATESTR VARCHAR(20);
I_YEAR VARCHAR(20);
I_MONTH VARCHAR(20);
I_DAY VARCHAR(20);
BEGIN
V1_DATESTR := REGEXP_REPLACE(TO_CHAR(V_DATESTR), ‘[^0-9]+’, ’ ');

I_YEAR := CASE
WHEN LENGTH(REGEXP_REPLACE(V1_DATESTR, ‘[^0-9]+’)) = 8 THEN
SUBSTR(REGEXP_REPLACE(V1_DATESTR, ‘[^0-9]+’), 1, 4)
WHEN LENGTH(REGEXP_REPLACE(V1_DATESTR, ‘[^0-9]+’)) != 8 THEN
REGEXP_SUBSTR(V_DATESTR, ‘[0-9]+’, 1, 1)
ELSE
NULL
END;
I_MONTH := CASE
WHEN LENGTH(REGEXP_REPLACE(V1_DATESTR, ‘[^0-9]+’)) = 8 THEN
SUBSTR(REGEXP_REPLACE(V1_DATESTR, ‘[^0-9]+’), 5, 2)
WHEN LENGTH(REGEXP_REPLACE(V1_DATESTR, ‘[^0-9]+’)) != 8 THEN
REGEXP_SUBSTR(V_DATESTR, ‘[0-9]+’, 1, 2)
ELSE
NULL
END;
I_DAY := CASE
WHEN LENGTH(REGEXP_REPLACE(V1_DATESTR, ‘[^0-9]+’)) = 8 THEN
SUBSTR(REGEXP_REPLACE(V1_DATESTR, ‘[^0-9]+’), 7, 2)
WHEN LENGTH(REGEXP_REPLACE(V1_DATESTR, ‘[^0-9]+’)) != 8 THEN
REGEXP_SUBSTR(V_DATESTR, ‘[0-9]+’, 1, 3)
ELSE
NULL
END;

V2_DATESTR := CASE
WHEN I_YEAR IS NULL THEN
NULL
ELSE
I_YEAR
END || CASE
WHEN LENGTH(I_MONTH) = 1 THEN
‘0’ || I_MONTH
ELSE
I_MONTH
END || CASE
WHEN LENGTH(I_DAY) = 1 THEN
‘0’ || I_DAY
WHEN I_DAY IS NULL THEN
‘01’
ELSE
I_DAY
END;

IF I_MONTH NOT BETWEEN 1 AND 12
THEN
BEGIN
RETURN NULL;
END;
END IF;

– 对日期的判断，1，3，5，7，8，10，12月最大日为31，4，6，9，11月最大日为30，2月若为闰年则为29，其它年则为28.
IF I_DAY BETWEEN 1 AND 31
OR I_MONTH IS NOT NULL
AND I_DAY IS NULL
THEN
BEGIN
IF I_DAY = 31
AND I_MONTH NOT IN (1, 3, 5, 7, 8, 10, 12)
THEN
BEGIN
RETURN NULL;
END;
END IF;
IF I_MONTH = 2
THEN
BEGIN
– RULES 1：普通年能被4整除且不能被100整除的为闰年。
– RULES 2：世纪年能被400整除的是闰年。
– RULES 3：对于数值很大的年份,这年如果能整除3200,并且能整除172800则是闰年。如172800年是闰年，86400年不是闰年。
IF ((MOD(I_YEAR, 4) = 0 AND MOD(I_YEAR, 100) <> 0) OR
MOD(I_YEAR, 400) = 0 OR
(MOD(I_YEAR, 3200) = 0 AND MOD(I_YEAR, 172800) = 0))
THEN
BEGIN
–若为闰年，则2月份最大日为29
IF I_DAY > 29
THEN
BEGIN
RETURN NULL;
END;
END IF;
END;
ELSE
BEGIN
–若不为闰年，则2月份最大日为28
IF I_DAY > 28
THEN
BEGIN
RETURN NULL;
END;
END IF;
END;
END IF;
END;
END IF;
RETURN V2_DATESTR;
END;
ELSE
RETURN NULL;
END IF;

/*
SELECT CASE
WHEN REGEXP_SUBSTR(V1_DATESTR, ‘[0-9]’) IS NOT NULL THEN
SUBSTR(V1_DATESTR, 1, 4) || CASE
WHEN REGEXP_INSTR(V1_DATESTR, ‘[^0-9]’, 1, 2) > 0 AND
LENGTH(SUBSTR(V1_DATESTR,
REGEXP_INSTR(V1_DATESTR, ‘[^0-9]’, 1, 1) + 1,
REGEXP_INSTR(V1_DATESTR, ‘[^0-9]’, 1, 2) -
REGEXP_INSTR(V1_DATESTR, ‘[^0-9]’, 1, 1) - 1)) = 1 THEN
‘0’ ||
SUBSTR(V1_DATESTR,
REGEXP_INSTR(V1_DATESTR, ‘[^0-9]’, 1, 1) + 1,
REGEXP_INSTR(V1_DATESTR, ‘[^0-9]’, 1, 2) -
REGEXP_INSTR(V1_DATESTR, ‘[^0-9]’, 1, 1) - 1)
WHEN REGEXP_INSTR(V1_DATESTR, ‘[^0-9]’, 1, 2) > 0 AND
LENGTH(SUBSTR(V1_DATESTR,
REGEXP_INSTR(V1_DATESTR, ‘[^0-9]’, 1, 1) + 1,
REGEXP_INSTR(V1_DATESTR, ‘[^0-9]’, 1, 2) -
REGEXP_INSTR(V1_DATESTR, ‘[^0-9]’, 1, 1) - 1)) = 2 THEN
SUBSTR(V1_DATESTR,
REGEXP_INSTR(V1_DATESTR, ‘[^0-9]’, 1, 1) + 1,
REGEXP_INSTR(V1_DATESTR, ‘[^0-9]’, 1, 2) -
REGEXP_INSTR(V1_DATESTR, ‘[^0-9]’, 1, 1) - 1)
WHEN REGEXP_INSTR(V1_DATESTR, ‘[^0-9]’, 1, 2) = 0 AND
LENGTH(SUBSTR(V1_DATESTR,
REGEXP_INSTR(V1_DATESTR, ‘[^0-9]’, 1, 1) + 1)) = 1 THEN
‘0’ ||
SUBSTR(V1_DATESTR, REGEXP_INSTR(V1_DATESTR, ‘[^0-9]’, 1, 1) + 1)
WHEN REGEXP_INSTR(V1_DATESTR, ‘[^0-9]’, 1, 2) = 0 AND
LENGTH(SUBSTR(V1_DATESTR,
REGEXP_INSTR(V1_DATESTR, ‘[^0-9]’, 1, 1) + 1)) = 2 THEN
SUBSTR(V1_DATESTR, REGEXP_INSTR(V1_DATESTR, ‘[^0-9]’, 1, 1) + 1)
ELSE
NULL
END || CASE
WHEN REGEXP_INSTR(V1_DATESTR, ‘[^0-9]’, 1, 2) > 0 AND
LENGTH(REGEXP_SUBSTR(SUBSTR(V1_DATESTR,
REGEXP_INSTR(V1_DATESTR,
‘[^0-9]’,
1,
2) + 1),
‘[0-9]’)) = 1 THEN
‘0’ ||
REGEXP_SUBSTR(SUBSTR(V1_DATESTR,
REGEXP_INSTR(V1_DATESTR, ‘[^0-9]’, 1, 2) + 1),
‘[0-9]’)
WHEN REGEXP_INSTR(V1_DATESTR, ‘[^0-9]’, 1, 2) > 0 AND
LENGTH(REGEXP_SUBSTR(SUBSTR(V1_DATESTR,
REGEXP_INSTR(V1_DATESTR,
‘[^0-9]’,
1,
2) + 1),
‘[0-9]’)) = 2 THEN
REGEXP_SUBSTR(SUBSTR(V1_DATESTR,
REGEXP_INSTR(V1_DATESTR, ‘[^0-9]’, 1, 2) + 1),
‘[0-9]’)
ELSE
NULL
END
ELSE
NULL
END
INTO V2_DATESTR
FROM DUAL;
RETURN V2_DATESTR;*/

END;