一道题, 有一定难度
如何查询出一个字段中是日期格式的数
INSERT INTO W_YJTABLE(WID,WNAME) VALUES(W_YJTABLE_SEQ.nextval,'12345');
INSERT INTO W_YJTABLE(WID,WNAME) VALUES(W_YJTABLE_SEQ.nextval,'12A45');
INSERT INTO W_YJTABLE(WID,WNAME) VALUES(W_YJTABLE_SEQ.nextval,'12');
INSERT INTO W_YJTABLE(WID,WNAME) VALUES(W_YJTABLE_SEQ.nextval,'ABCE');
INSERT INTO W_YJTABLE(WID,WNAME) VALUES(W_YJTABLE_SEQ.nextval,'A234');
INSERT INTO W_YJTABLE(WID,WNAME) VALUES(W_YJTABLE_SEQ.nextval,'1998-02-02');
INSERT INTO W_YJTABLE(WID,WNAME) VALUES(W_YJTABLE_SEQ.nextval,'-02-02');
INSERT INTO W_YJTABLE(WID,WNAME) VALUES(W_YJTABLE_SEQ.nextval,'中');如查询出数据'1998-02-02'
如何查询出一个字段中是日期格式的数
INSERT INTO W_YJTABLE(WID,WNAME) VALUES(W_YJTABLE_SEQ.nextval,'12345');
INSERT INTO W_YJTABLE(WID,WNAME) VALUES(W_YJTABLE_SEQ.nextval,'12A45');
INSERT INTO W_YJTABLE(WID,WNAME) VALUES(W_YJTABLE_SEQ.nextval,'12');
INSERT INTO W_YJTABLE(WID,WNAME) VALUES(W_YJTABLE_SEQ.nextval,'ABCE');
INSERT INTO W_YJTABLE(WID,WNAME) VALUES(W_YJTABLE_SEQ.nextval,'A234');
INSERT INTO W_YJTABLE(WID,WNAME) VALUES(W_YJTABLE_SEQ.nextval,'1998-02-02');
INSERT INTO W_YJTABLE(WID,WNAME) VALUES(W_YJTABLE_SEQ.nextval,'-02-02');
INSERT INTO W_YJTABLE(WID,WNAME) VALUES(W_YJTABLE_SEQ.nextval,'中');如查询出数据'1998-02-02'
select * from W_YJTABLE where to_date(WNAME,'YYYY.MM.DD') Between to_date('1900.01.01','YYYY.MM.DD') and to_date('2099.12.31','YYYY.MM.DD')
select WNAME from W_YJTABLE WHERE WNAME LIKE '____-__-__';
完成。。给分
select WNAME from W_YJTABLE WHERE WNAME LIKE '____-__-__';
还有一种方法是写个函数把字段传进去,to_date一下
正确返回个数比如说1不正确返回0然后select都是1的就哦了。
select wname from w_yjtable where regexp_like(wname,'^[[:digit:]]{4}-[[:digit:]]{1,2}-[[:digit:]]{1,2}$');该SQL语句能检测出WNAME的格式为:yyyy-mm-dd / yyyy-mm-d / yyyy-m-d / yyyy-m-dd的所有数据.
报错,看来大家都没想到我是想写一个自定义函数 像Oracle中IsDate函数功能一样
SQL> select * from dinya_01 a where REGEXP_LIKE(a.s_value,'^\d{2,4}-\d{1,2}-\d{1,2}$')
2 /S_VALUE
--------------------------------------------------------------------------------
1998-02-02
1998-2-2
98-2-2SQL> ---我想, 如果是10g数据库的话, 这应该可以满足你的查询需求了.