关于oracle日期相关的查询条件

Question

有一列number(8)类型的数据，存的日期数据，可能有不符合规范的数据，比如说大部分是年月日这种存储的，但也有不是这种存储的，现在想找出来这些不符合规范的数据，条件是这里面是否按照年月日格式存的，还有就是是否不在某个日期区间内

例：

数据日期
20210331
03312021
31032021
19990101
21000101

如上表所示，我想查询这个表，这个列是number(8)类型的数据，但是存的是日期，我想判断里面的数据是不是都是按照年月日存储的，是否在20000101-20301231这个日期区间内，用一条查询sql能实现嘛？怎么实现？

Answer 1

这个问题关键在于你要只是判断还是要提取出这部分数据，通过数字是没有办法判断时间是否符合要求的，比如20210229，你就无法知道它是否是有效的日期。因此必须转换为时间。
如果是要把不符合的数据提取出来，一条SQL无法搞定，需要写存储过程或代码块，建议代码块，可以程序直接调用。在存储过程或代码块中批量提取数据，然后循环判断，如果出错了或者不符合要求则记录数据。
大致伪代码是这样的
declare
变量 自定义类型;
转换后日期数据 date;
begin
select 字段1，字段2 bulck collect into 变量 from table；
for i in 1..变量.count loop
BEGIN
转换后日期数据:=to_date(变量.XXX,'YYYY-MM-DD');
if 转换后日期数据 <区间日期开始 and >区间日期结束 then
//记录不在指定区间的数据
end if;
EXCEPTION WHEN OTHERS THEN
记录不符合日期的数据。
END
end loop;

但是如果只是判断有没有不符合的数据，那么可以使用一条SQL搞定。
思路是这样的

select count(*) from table where to_date(数据日期，‘yyyy-mm-dd’) between 开始日期 and 结束日期

如果结果报错了，那么意味着有不符合要求的数据