oracle 时间表达式,正则表达式判断日期是否合法

改了一下，日期格式为 YYYYMMDD, 反向引用减少到只有2个，性能好一点，0000年已经视作错误

SQL> select * from t;

MYDATE

--------

19000229

19040229

20000229

24000229

24010331

14010332

00000101

20110721

20110228

20110229

00000228

20110530

20111231

20110831

20110731

20110631

日期格式YYYYMMDD

select * from t where regexp_like(mydate,'(\d{3}[^0]|[^0]\d{3})(0[^0]0[1-9]|0[^0]1[0-9]|0[^0]2[0-8]|1[0-2]0[1-9]|1[0-2]1[0-9]|1[0-2]2[0-8]|0[13-9]29|1[0-2]29|0[13-9]30|1[0-2]30|0[13578]31|1[02]31)|\d{2}0[48]0229|\d{2}[2468][048]0229|\d{2}[13579][26]0229|0[48]000229|[13579][26]000229|[2468][048]000229');

MYDATE

--------

19040229

20000229

24000229

24010331

20110721

20110228

20110530

20111231

20110831

20110731

10 rows selected

-------------------------------------------------------------

create table t(mydate varchar2(8));

insert into t (MYDATE) values ('19000229');

insert into t (MYDATE) values ('19040229');

insert into t (MYDATE) values ('20000229');

insert into t (MYDATE) values ('24000229');

insert into t (MYDATE) values ('24010331');

insert into t (MYDATE) values ('14010332');

insert into t (MYDATE) values ('00000101');

insert into t (MYDATE) values ('20110721');

insert into t (MYDATE) values ('20110228');

insert into t (MYDATE) values ('20110229');

insert into t (MYDATE) values ('00000228');

insert into t (MYDATE) values ('20110530');

insert into t (MYDATE) values ('20111231');

insert into t (MYDATE) values ('20110831');

insert into t (MYDATE) values ('20110731');

insert into t (MYDATE) values ('20110631');

commit;

[本帖最后由 atgc 于 2011-7-25 18:43 编辑]